Copy autofiltered range, vba excel

Question

I have written the code that does the following:

applies autofilter to specific sheet in the selected workbook
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.

EDIT

Here is the example of the data in worksheet

example of data

Filter is applied to Criteria1 (<> 60, <>50) and to Criteria2 (<>1470, <>1450)

Stewbob Stewbob · Accepted Answer · 2013-09-17T19:25:39

.UsedRange will grab all the data on your source sheet, not just data below the auto-filtered results.

The Offset that you use in your Intersect...Copy statement should be the number of rows above your auto-filtered results that you want to ignore, instead of the value 1.

If you know how many header rows you have:

numHeaderRows = 5
For i = 1 To work_book.Worksheets.Count 
  With work_book.Sheets(i) 
    If (.UsedRange.Rows.Count > 1) Then 
         'apply auto-filters starting at the row directly above the start of the data.
        .UsedRange.Offset(numHeaderRows-1).AutoFilter field:=2, Criteria1:=array_of_account_numbers, Operator:=xlFilterValues 
        .UsedRange.Offset(numHeaderRows-1).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(numHeaderRows)).SpecialCells(xlCellTypeVisible).Copy destination_workbook.Sheets(1).Range("A" & m) 
  a: 
    End If 
  End With
Next

Copy autofiltered range, vba excel

1 Answers