Tuesday, 17 September 2013

Copy autofiltered range, vba excel

Copy autofiltered range, vba excel

I have written the code that does the following: 1) applies autofilter to
specific sheet in the selected workbook 2) copies data from autofiltered
range except the header to another workbook
Here is the code:
m = 2
For i = 1 To work_book.Worksheets.Count
With work_book.Sheets(i)
If (.UsedRange.Rows.Count > 1) Then
'apply filters
.UsedRange.AutoFilter field:=2,
Criteria1:=array_of_account_numbers, Operator:=xlFilterValues
.UsedRange.AutoFilter field:=1,
Criteria1:=array_of_debit_or_credits, Operator:=xlFilterValues
'select only visible cells after autofilter is applied
On Error Goto a
m = destination_workbook.Sheets(1).UsedRange.Rows.Count + 1
Intersect(.UsedRange,
.UsedRange.Offset(1)).SpecialCells(xlCellTypeVisible).Copy
destination_workbook.Sheets(1).Range("A" & m)
a:
End If
End With
However, macro persistently copies some garbage. It means that it copies
from each sheet first three rows in addition to autofiltered range. How
can I solve this issue? I will appreciate for your help and your answers.

No comments:

Post a Comment