Select Method of Worksheet Class Failed (Unsure why)

Question

Basically I open up a seperate sheet, and for each workbook in this sheet I hide some cells and filter it. Afterwards I need to copy this filtered data into the main workbook. For some reason I am getting a Select Method of Worksheet Class Failed Error and I have been stuck on it for a while. The code is below. Thank you in advance!

Sub MonthlyActivityThirdBusinessDay()
    Dim FeeReport As Variant
    Dim FeeReportWB As Workbook
    Dim CopyLastRow As Long
    
    

    

    Application.ScreenUpdating = False
    Application.DisplayAlerts = False
    'Improves Performance
    
    
    FeeReport = Application.GetOpenFilename(Title:="Select your Fee Report", FileFilter:="Excel Files(*.xlsx*),*xlsx*")
  
        Set FeeReportWB = Application.Workbooks.Open(FeeReport)
    
    Dim TotalSheets As Long
    TotalSheets = FeeReportWB.Sheets.Count
    
    For i = 1 To TotalSheets
        ' I am getting the error here, not sure which cycle of my loop 
        FeeReportWB.Sheets("Page1_" & i).Select
        Columns("A").EntireColumn.Hidden = True
        Columns("C:F").EntireColumn.Hidden = True
        Columns("H:S").EntireColumn.Hidden = True
        Columns("B").EntireColumn.Hidden = False
        Columns("G").EntireColumn.Hidden = False
        Columns("B").EntireColumn.Hidden = False
        
        'filter for Non Managed Fee Based
        
        Range("G2").Select
        Selection.AutoFilter
        ActiveSheet.Range("$A$2:$T$65000").AutoFilter Field:=7, Criteria1:= _
            "Non Managed Fee Based"
        'Hide the column
        Columns("G").EntireColumn.Hidden = True
        
        'Copy the filtered workbook but have to hide rows 1 and 2 first because I am copying all visible cells
        Rows("1:2").EntireRow.Hidden = True
        
        Range("A1:T65000").SpecialCells(xlCellTypeVisible).Copy
        Workbooks("Test Fee Deduction Plan Master List.xlsm").Activate
        Sheets("Paste Reporting Here").Select
        
        'paste at the end of the current data
        RowCount = Cells(Rows.Count, 1).End(xlUp).Row
        ActiveSheet.Paste Destination:=Worksheets("Paste Reporting Here").Range("A" & RowCount)
        
        Next i
    
        
    
End Sub

[You don't really need to `Select`](https://stackoverflow.com/questions/10714251/how-to-avoid-using-select-in-excel-vba). — BigBen, Feb 26 '21 at 19:59
`Dim ws as Worksheet`, `For Each ws in FeeReportWb.Worksheets`. No need to refer to the sheet by name either. — BigBen, Feb 26 '21 at 19:59

score 1 · Answer 1 · answered Feb 26 '21 at 20:11

i edit your code and delete selections. hope it helped, i didnt test it.

Sub MonthlyActivityThirdBusinessDay()

    Dim FeeReport As Variant
    Dim FeeReportWB As Workbook
    Dim CopyLastRow As Long
    Dim ws As Worksheet, wsDest As Worksheet
    
    Application.ScreenUpdating = False
    Application.DisplayAlerts = False
    'Improves Performance
    FeeReport = Application.GetOpenFilename(Title:="Select your Fee Report", FileFilter:="Excel Files(*.xlsx*),*xlsx*")
    If FeeReport <> False Then Set FeeReportWB = Application.Workbooks.Open(FeeReport) Else Exit Sub
    
    Dim TotalSheets As Long
    TotalSheets = FeeReportWB.Sheets.Count

    Set wsDest = Workbooks("Test Fee Deduction Plan Master List.xlsm").Sheets("Paste Reporting Here")

    For i = 1 To TotalSheets
        Set ws = FeeReportWB.Worksheets(i)
        ' I am getting the error here, not sure which cycle of my loop
        ws.Columns("A").EntireColumn.Hidden = True
        ws.Columns("C:F").EntireColumn.Hidden = True
        ws.Columns("H:S").EntireColumn.Hidden = True
        ws.Columns("B").EntireColumn.Hidden = False
        ws.Columns("G").EntireColumn.Hidden = False
        ws.Columns("B").EntireColumn.Hidden = False
        
        'filter for Non Managed Fee Based
        If ws.AutoFilter = True Then ws.AutoFilter = False
        ws.Range("G2").AutoFilter
        ws.Range("$A$2:$T$65000").AutoFilter Field:=7, Criteria1:= _
            "Non Managed Fee Based"
        'Hide the column
        ws.Columns("G").EntireColumn.Hidden = True
        
        'Copy the filtered workbook but have to hide rows 1 and 2 first because I am copying all visible cells
        ws.Rows("1:2").EntireRow.Hidden = True
        
        RowCount = wsDest.Cells(Rows.Count, 1).End(xlUp).Row + 1
        ws.Range("A1:T65000").SpecialCells(xlCellTypeVisible).Copy wsDest.Cells(RowCount, "A")
        Set ws = Nothing
    Next i
    
    Application.ScreenUpdating = True
    Application.DisplayAlerts = True
End Sub

For If ws.AutoFilter = True Then , I am getting Object doesn't support this property or method error — orange123, Feb 26 '21 at 20:15

Select Method of Worksheet Class Failed (Unsure why)

1 Answers1