GB Allan


Hi,

I have an excellent macro that I wrote, but it has a slight hitch. I have it first filtering the data, then copying out the records that show up after filtering, to another workbook. The problem is if there are no filtered records. It then copies over the field headings (which I don't want). Even when I put it at range ("A2"), it still selects and copies the column headings when there are no visible records.

What I would like it to do is check to see if any records are visible after filtering. If so, then do the copy. If not, skip the copy. I don't want it to copy the column headings. Below is a snippet. Thank you,

Gary

ThisWorkbook.Activate
Application.CutCopyMode = False
Selection.AutoFilter Field:=4, Criteria1:="Donald Duck"
Range("A2").Select
Range(Selection, ActiveCell.SpecialCells(xlLastCell)).Select
Selection.Copy
ChDir "C:\"
Workbooks.Open Filename:="C:\DataFile.xls"
Sheets("NewData").Select
Range("A11").Select
ActiveSheet.Paste
Application.CutCopyMode = False




Re: Macro to only copy visible filtered rows

bi-lya


Hi!
If you transfer filtering data without the column headings then try like this

Code Block

....

Range(Selection, ActiveCell.SpecialCells(xlLastCell)).Select

If ActiveCell.Address(0, 0) = "A1" Then' or see what cell is active

...

Exit Sub

End If

....

If you transfer filtering data with the column headings then

Code Block

If Selection.Rows.Count = 1 Then ....