'Select method of Worksheet class failed' when trying to select visible cells in DataBodyRange after AutoFilter

Question

I've used exact same technique in the different module and worked like a charm. I've started new macro with the below code and it fails miserably as soon as trying to Select all the filtered cells to make a copy into the template file. It both use Tables so ListObjects. What am I doing wrong here?

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

Probably worth [avoiding Selecting](https://stackoverflow.com/questions/10714251/how-to-avoid-using-select-in-excel-vba) in the first place. — BigBen, Apr 06 '21 at 16:10
Is the sheet protected? I think use of special cells requires an unprotected sheet. — Senior Momentum, Apr 06 '21 at 16:19

score 2 · Accepted Answer · edited Jan 07 '22 at 07:58

Avoid the use of .Select as Bigben mentioned above. Also it is not necessary that if there are 32106 rows then after filter all of them are visible. You need to try and store the visible range in a range object (Ex: Set rng = MasterData.Sheets(1).ListObjects(1).DataBodyRange.SpecialCells(xlCellTypeVisible)) by sandwiching this line between On Error Resume Next and On Error Go To. After that you have to check If Not rng is nothing then rng.copy and then paste it in the destination sheet.

CODE

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

Perfect! This worked, however I do not understand why I could not select the cells that has been filtered as in the file it was clearly a lot visible cells, but the macro just ignored it? — Robert Jamborski, Apr 07 '21 at 09:33
There could me many reason for this. Worksheet being protected or hidden or anything that prevents the selection of the cells. And hence it is always better to [Avoid using Select](https://stackoverflow.com/questions/10714251/how-to-avoid-using-select-in-excel-vba) — Siddharth Rout, Apr 07 '21 at 09:52

'Select method of Worksheet class failed' when trying to select visible cells in DataBodyRange after AutoFilter

1 Answers1