复制自动过滤的范围，vba excel

Copy autofiltered range, vba excel

提问by fsua

I have written the code that does the following:

我编写了执行以下操作的代码：

1. applies autofilter to specific sheetin the selected workbook
2. copies data from autofiltered rangeexcept the header to another workbook

1. 将自动过滤器应用于sheet选定的特定workbook
2. 将数据从autofiltered range除标头之外复制到另一个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 sheetfirst three rowsin addition to autofiltered range.

但是，宏会持续复制一些垃圾。这意味着，它复制从每个sheet前三rows除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

这是工作表中的数据示例

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

过滤器应用于 Criteria1 (<> 60, <>50) 和 Criteria2 (<>1470, <>1450)

回答by Stewbob

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

.UsedRange将获取源工作表上的所有数据，而不仅仅是自动过滤结果下方的数据。

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

将Offset您在使用Intersect...Copy的说法应该是，而不是价值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