How to stop autofilter macro failing intermittently

Question

I have a macro which is designed to autofilter for certain criteria and then hide certain columns and copy what is left to the appropriate file. Sometimes the file filters correctly, but sometimes it stops on the Selection.AutoFilter line with a RE 1004 error, "Method of range class failed". This usually happens if I run the macro immediately after opening the file. If I reset the entire sheet with a macro I have to unhide everything, it filters correctly. If it does filter correctly, it omits certain columns when pasting to the destination file. Those columns are the first one right after a handful of blank ones. I need it to copy either all visible columns except the header, or can even be changed to columns A - X, as that is the extent of the information required.

Here is the macro

Sub OO_Away_Lay_1()
'
' OO Away Lay v1 Macro
' This macro will filter for 1x2
'
    Dim ws As Worksheet, lc As Long, lr As Long

    Set ws = ActiveSheet
    'range from A1 to last column header and last row
    lc = ws.Cells(1, ws.Columns.Count).End(xlToLeft).Column
    lr = ws.Cells.Find("*", after:=ws.Range("A1"), LookAt:=xlPart, _
                        SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
   
    With ws.Range("A1", ws.Cells(lr, lc))
        .HorizontalAlignment = xlCenter
        Selection.AutoFilter
        .AutoFilter Field:=24, Criteria1:="Draw", Operator:=xlFilterValues
        If .Rows.Count - 1 > 0 Then
        On Error Resume Next
        .Columns("L:S").EntireColumn.Hidden = True
        .Columns("U:W").EntireColumn.Hidden = True
        .Columns("Y:CK").EntireColumn.Hidden = True
        .Offset(1, 0).Resize(.Rows.Count - 1).SpecialCells(xlCellTypeVisible).Copy
        On Error GoTo 0
            Else
                Exit Sub
        End If
    End With
     
    Workbooks("Predictology_Trading Template v3.1.xlsm").Sheets("OO Away Lay v1") _
          .Range("A" & Rows.Count).End(xlUp).Offset(1).PasteSpecial xlPasteValues
   
    Application.CutCopyMode = False
End Sub

Any thoughts on fixing it so it autofilters all the time and also copies all of the required data?

cheers

Answer 1

等待大神答复