excel - 尝试在自动筛选后选择 DataBodyRange 中的可见单元格时，“工作表类的选择方法失败”

Question

我在不同的模块中使用了完全相同的技术，并且像一个魅力一样工作。我已经用下面的代码开始了新的宏，一旦尝试 Select，它就会惨遭失败。所有过滤的单元格都复制到模板文件中。它都使用 Tables所以ListObjects .我在这里做错了什么？

Sub ImportMasterData()
    Dim MasterData As Workbook
    Dim Template As Workbook
    
    Dim DateFrom As Date
    Dim DateTo As Date
    
    DateFrom = #2/4/2021#
    DateTo = #6/4/2021#
    
    Set MasterData = Workbooks.Open("C:/Users/RJamborski/Desktop/VBA/Master Archive.xlsx")
    Set Template = Workbooks.Open("C:/Users/RJamborski/Desktop/VBA/MasterArchiveTemplate.xlsx")
    
    With MasterData.Sheets(1).ListObjects(1).DataBodyRange
        .AutoFilter Field:=1, Criteria1:= _
            Array("Dav1 - Danes Way", "Dav2 - Rail Port", "Dav4 - MagnaParkLutterworth"), _
            Operator:=xlFilterValues
        .AutoFilter Field:=4, Criteria1:=">=" & DateFrom, _
            Operator:=xlAnd, Criteria2:="<=" & DateTo
    End With
    
    Debug.Print MasterData.Sheets(1).ListObjects(1).DataBodyRange.Rows.Count ' this prints 32106 
    MasterData.Sheets(1).ListObjects(1).DataBodyRange.SpecialCells(xlCellTypeVisible).Select ' but this throws an error
    
    Selection.Copy
    
    Template.Sheets(1).ListObjects(1).Range("A2").PasteSpecial Paste:=xlPasteValues, _
        Operation:=xlNone, SkipBlanks:=False, Transpose:=False
    
End Sub

Answer 1

避免使用 .Select正如上面提到的Bigben。如果有 32106 也没有必要。然后在过滤后所有行都是可见的。您需要尝试将可见范围存储在范围对象中(例如:Set rng = MasterData.Sheets(1).ListObjects(1).DataBodyRange.SpecialCells(xlCellTypeVisible))，方法是将这条线夹在 On Error Resume Next 之间和 On Error Go To .之后你必须检查 If Not rng is nothing then rng.copy然后将其粘贴到目标工作表中。
代码

Option Explicit

Sub ImportMasterData()
    Dim MasterData As Workbook
    Dim Template As Workbook
    
    Dim DateFrom As Date
    Dim DateTo As Date
    
    Dim rng As Range
    
    DateFrom = #2/4/2021#
    DateTo = #6/4/2021#
    
    Set MasterData = Workbooks.Open("C:/Users/RJamborski/Desktop/VBA/Master Archive.xlsx")
    Set Template = Workbooks.Open("C:/Users/RJamborski/Desktop/VBA/MasterArchiveTemplate.xlsx")
    
    With MasterData.Sheets(1).ListObjects(1).DataBodyRange
        .AutoFilter Field:=1, _
                    Criteria1:=Array("Dav1 - Danes Way", "Dav2 - Rail Port", _
                    "Dav4 - MagnaParkLutterworth"), _
                    Operator:=xlFilterValues
        .AutoFilter Field:=4, Criteria1:=">=" & DateFrom, _
                    Operator:=xlAnd, Criteria2:="<=" & DateTo
                    
        On Error Resume Next
        Set rng = .SpecialCells(xlCellTypeVisible)
        On Error GoTo 0
    End With
    
    If Not rng Is Nothing Then
        rng.Copy
        
        Template.Sheets(1).ListObjects(1).Range("A2").PasteSpecial Paste:=xlPasteValues, _
        Operation:=xlNone, SkipBlanks:=False, Transpose:=False
    Else
        MsgBox "No Visible range found"
    End If
End Sub