subscript out of range

[dpaton05]

Why do I get the error subscript out of range and this line is highlighted.

.AutoFilter 1, LCDt

Sub LateCancel()

        Dim ws As Worksheet, sh As Worksheet, sht As Worksheet, wb As Workbook, wb2 As Workbook, WbPath As String, QTPath As String
        Set wb2 = ThisWorkbook
        Set sh = Sheets("Totals")
        'Set sht = Sheets("Cancellations")
        Dim LCReq As String: LCReq = sh.[B32].Value
        Dim LCDt As String: LCDt = sh.[B34].Value
        WbPath = ThisWorkbook.Path
        'CurDir ".."
        'CurDir ".."
        'QTPath = Left(WbPath, InStrRev(WbPath, "\") - 2)
        QTPath = ThisWorkbook.Path & "\..\" & "\..\"
Application.ScreenUpdating = False
        'If Not isFileOpen(DocYearName & ".xlsm") Then Workbooks.Open ThisWorkbook.Path & "\" & "Work Allocation Sheets" & "\" & Site & "\" & DocYearName & ".xlsm"
        
        If Not isFileOpen("CSS_quoting_tool_29.5.xlsm") Then Workbooks.Open QTPath & "\" & "CSS_quoting_tool_29.5.xlsm"
    
        For Each ws In Worksheets
                If ws.Name <> "Cancellations" And ws.Name <> "Totals" Then
                        With ws.[A3].CurrentRegion
                                .AutoFilter 1, LCDt
                                .AutoFilter 3, LCReq
                                .Offset(1).EntireRow.Copy sh.Range("A" & Rows.Count).End(xlUp).Offset(1)
                                .Offset(1).EntireRow.Delete
                                .AutoFilter
                        End With
                End If
        Next ws
        
sh.Range("B32,B34").ClearContents
Application.ScreenUpdating = True

End Sub

 

[Michael M]

3 options
1. One of your sheets is named incorrectly
2. there is nothing in LCDt
3. there is nothing in the column of sh.Range("A" & Rows.Count).End(xlUp)...even a header should fix that

 

[dpaton05]

I changed the start of the sub to be

Set sh = wb2.Sheets("Totals")

...and that seems to have removed that error

I have also changed to use .cells instead of .range

        Dim LCReq As String: LCReq = sh.Cells(32, 2).Value
        Dim LCDt As String: LCDt = sh.Cells(34, 2).Value

but now i try and run the sub and I get the error Autofilter method of range class failed, I press debug and it highlights:

.AutoFilter 1, LCDt

and if I hover over LCDt, I get the tool tip of "".

What does this mean?

By the way, I have hardly any data in my workbook at the moment at this stage.

 

[dpaton05]

I just worked that bit out

 

[Michael M]

Yep.......point number 2 in my previous post !!