将过滤的行复制到另一张工作表 [英] Copy filtered rows to another sheet

问题描述

我想将过滤后的行复制到另一张纸上，但以下代码出现错误.我在E栏中拥有三个状态，即打开"，关闭"和过期".我想复制状态为未清或过期的所有行.

I want to copy the filtered row to another sheets but getting an error on below code. I have three status in column E i.e Open, Close and Overdue. I want to copy all the rows having status as open or overdue.

错误发生在下面的行

.AutoFilter Field:=5, Criteria1:="open"

这是代码

Dim Wb As Workbook
Dim ws As Worksheet
Set Wb = Workbooks.Open(TextBox2.Text)
Set ws = Wb.Sheets(strSheetName)

With ws
    .AutoFilterMode = False
    With .Range("A1:E65536")
        .AutoFilter Field:=5, Criteria1:="open"
        .SpecialCells(xlCellTypeVisible).copy Destination:=Sheets("Master Records").Range("A1")
    End With
End With

好吧，因为我是新手，所以我没有说错误是什么，也没有说输入工作簿的来源……所以也许我的用户给了我格式错误的内容，而在工作表中没有过滤器...

Well, since I'm new I didn't say what was the error, nor where the input workbook where coming from... So maybe my users give me ill formatted stuff, without filter in the sheet that should be...

推荐答案

有了您的代码，我得到了通常的

With your code, I get the usual

Run-time error '1004'

但更具体地说

 AutoFilter method of Range class failed

因为我的工作表中没有过滤器.

因此，这里是为工作表添加过滤器:

Because there was no filter in my sheet.

So here is to add the filter for the sheet:

Sub Macro3()
  Dim Wb As Workbook
  Dim ws As Worksheet
  Set Wb = ThisWorkbook
  Set ws = Wb.Sheets("Sheet1")

  ' here is what to add:      
  Range("A1:E1").Select
  Selection.AutoFilter
  ' done

  With ws
      .AutoFilterMode = False
      With .Range("A1:E65536")
          .AutoFilter Field:=6, Criteria1:="open"
          .SpecialCells(xlCellTypeVisible).Copy Destination:=Sheets("Master Records").Range("A1")
...
      End With
  End With
  
End Sub

这篇关于将过滤的行复制到另一张工作表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！