VBA Error

[Lelewiwi]

Hello helpful peeps! I have a massive macro that is run every month on files that are received from an outside source. It worked last month, but this month I received an error. The file received has not changed, nor has the code. I don't get it. Any help is appreciated! The line where the code is red.

    'Clean and set up EB Other Locals Adjustment workbook
        Dim lr8 As Long
        Dim lr9 As Long
        Sourcewb2.Activate
        Columns("D").Delete

    'Filter, copy, paste
        lr8 = Cells(Rows.count, "A").End(xlUp).Row
        Columns("K:K").AutoFilter
        ActiveSheet.Range("$A$1:$P$" & lr8).AutoFilter Field:=11, Criteria1:="<0"
        Range("$A$1:$P$" & Cells(Rows.count, "A").End(xlUp).Row).SpecialCells(xlCellTypeVisible).Copy
        ThisWorkbook.Activate
        Sheets("Eastbay Adjustments").Select
        lr9 = Cells(Rows.count, "A").End(xlUp).Row
        Cells(Rows.count, 1).End(xlUp).Offset(2, 0).PasteSpecial xlPasteValues
        Application.CutCopyMode = False

 

[rlv01]

I received an error

There are hundreds of possible errors. A best practice when asking for help with an error is to include the name, number, description, and line location of the specific error in your post. You have provided the last one but not anything else.

 

[Lelewiwi]

Sorry about that, I am in a bit of a panic. The error I got is "Run-time error '1004': AutoFilter method of Range class failed."

 

[Joe4]

What exactly is the value of "lr8" when you get this error message?

 

[Lelewiwi]

It varies every month. I have this exact code working correctly on another file just above this. The whole code worked last month. I just don't get why it isn't working this month.

'Eastbay Local 70

    'Clean and set up EB Local 70 Adjustment workbook
        Dim lr6 As Long
        Dim lr7 As Long
        Sourcewb.Activate
        Columns("D").Delete

    'Filter, copy, paste
        lr6 = Range("A" & Rows.count).End(xlUp).Row
        ActiveSheet.Range("$A$1:$P$" & lr6).AutoFilter Field:=11, Criteria1:="<0"
        Range("$A$1:$P$" & Cells(Rows.count, "A").End(xlUp).Row).SpecialCells(xlCellTypeVisible).Copy
        ThisWorkbook.Activate
        Sheets("Eastbay Adjustments").Select
        Range("A1").Select
        Cells(Rows.count, 1).End(xlUp).Offset(1, 0).PasteSpecial xlPasteValues
        Application.CutCopyMode = False

    'Delete unhidden data
        Sourcewb.Activate
        Application.DisplayAlerts = False
        ActiveSheet.UsedRange.Offset(1, 0).Resize(ActiveSheet.UsedRange.Rows.count - 1).Rows.Delete
        Application.DisplayAlerts = True

    'Remove filter
        Range("K:K").AutoFilter

    'Save EB 70 Adjustment workbook to new name
        Application.DisplayAlerts = False
        MsgBox "Save as 'Eastbay_70_Adj' to be sent to Operations.", vbOKOnly
        Dim File_Name As String
        File_Name = Application.GetSaveAsFilename
        ActiveWorkbook.SaveAs Filename:=File_Name & "xlsx"
        ActiveWorkbook.Saved = True
        ActiveWorkbook.Close
        Application.DisplayAlerts = True

'Eastbay Other Locals

    'Clean and set up EB Other Locals Adjustment workbook
        Dim lr8 As Long
        Dim lr9 As Long
        Sourcewb2.Activate
        Columns("D").Delete

    'Filter, copy, paste
        lr8 = Cells(Rows.count, "A").End(xlUp).Row
        Columns("K:K").AutoFilter
        ActiveSheet.Range("$A$1:$P$" & lr8).AutoFilter Field:=11, Criteria1:="<0"
        Range("$A$1:$P$" & Cells(Rows.count, "A").End(xlUp).Row).SpecialCells(xlCellTypeVisible).Copy
        ThisWorkbook.Activate
        Sheets("Eastbay Adjustments").Select
        lr9 = Cells(Rows.count, "A").End(xlUp).Row
        Cells(Rows.count, 1).End(xlUp).Offset(2, 0).PasteSpecial xlPasteValues
        Application.CutCopyMode = False

 

[Joe4]

No, I mean in this particular example where you are getting the error!

You could temporarily update your code like this to easily find out:

    'Filter, copy, paste
        lr8 = Cells(Rows.count, "A").End(xlUp).Row
        MsgBox lr8
        Columns("K:K").AutoFilter
        ActiveSheet.Range("$A$1:$P$" & lr8).AutoFilter Field:=11, Criteria1:="<0"

The Message Box will return the value of lr8.
Can you tell us exactly what it is returning?

 

[Lelewiwi]

62

 

[Joe4]

OK, so what kind of data is in column K?
Is it all numeric from cells K1:K62?
Are there are any errors anywhere in your data?

And does the sheet already have any filters on it before you are trying to apply this filter?

 

[Lelewiwi]

All numbers except for the header.

 

[Joe4]

And are you sure that:
- you are looking at the correct sheet (whatever "Sourcewb2" is set to)?
- there are currently no filters already applied to this data?
- there are no merged cells in your data?
- you don't have any sheet protection turned on for this sheet?