Vba issue run time error 91

[MZING81]

Here's a code that I've been working on. It will filter across all sheets, but wont remove the filter past the active sheet. Also in the highlighted area is a runtime error 91: Object variable or with block variable not set

[COLOR=#00b050][SIZE=3][FONT=Calibri][COLOR=black]Sub filter()<?xml:namespace prefix = o ns = "urn:schemas-microsoft-com:office:office" /><o:p></o:p>[/COLOR][/FONT][/SIZE][/COLOR]
[COLOR=#00b050][SIZE=3][FONT=Calibri][COLOR=black]  Dim ws As Worksheet<o:p></o:p>[/COLOR][/FONT][/SIZE][/COLOR]
[COLOR=#00b050][SIZE=3][FONT=Calibri][COLOR=black]    Application.ScreenUpdating = False<o:p></o:p>[/COLOR][/FONT][/SIZE][/COLOR]
[COLOR=#00b050][SIZE=3][FONT=Calibri][COLOR=black]    Application.Calculation = xlCalculationManual<o:p></o:p>[/COLOR][/FONT][/SIZE][/COLOR]
[COLOR=#00b050][SIZE=3][FONT=Calibri][COLOR=black]    For Each ws In Worksheets<o:p></o:p>[/COLOR][/FONT][/SIZE][/COLOR]
[COLOR=#00b050][SIZE=3][FONT=Calibri][COLOR=black]        With ws<o:p></o:p>[/COLOR][/FONT][/SIZE][/COLOR]
[COLOR=#00b050][SIZE=3][FONT=Calibri][COLOR=black]            .UsedRange.UnMerge<o:p></o:p>[/COLOR][/FONT][/SIZE][/COLOR]
[COLOR=#00b050][SIZE=3][FONT=Calibri][COLOR=black]            .Rows("8:8").AutoFilter<o:p></o:p>[/COLOR][/FONT][/SIZE][/COLOR]
[COLOR=#00b050][SIZE=3][FONT=Calibri][COLOR=black]            With .AutoFilter<o:p></o:p>[/COLOR][/FONT][/SIZE][/COLOR]
[COLOR=#00b050][SIZE=3][FONT=Calibri][COLOR=black]              With .Sort <o:p></o:p>[/COLOR][/FONT][/SIZE][/COLOR]
[COLOR=#00b050][SIZE=3][FONT=Calibri][COLOR=black].SortFields.Clear<o:p></o:p>[/COLOR][/FONT][/SIZE][/COLOR]
[COLOR=#00b050][SIZE=3][FONT=Calibri][COLOR=black]                    .SortFields.Add Key:=Range("D8"), _<o:p></o:p>[/COLOR][/FONT][/SIZE][/COLOR]
[COLOR=#00b050][SIZE=3][FONT=Calibri][COLOR=black]                        SortOn:=xlSortOnValues, _<o:p></o:p>[/COLOR][/FONT][/SIZE][/COLOR]
[COLOR=#00b050][SIZE=3][FONT=Calibri][COLOR=black]                        Order:=xlAscending, _<o:p></o:p>[/COLOR][/FONT][/SIZE][/COLOR]
[COLOR=#00b050][SIZE=3][FONT=Calibri][COLOR=black]                        DataOption:= _<o:p></o:p>[/COLOR][/FONT][/SIZE][/COLOR]
[COLOR=#00b050][SIZE=3][FONT=Calibri][COLOR=black]                        xlSortNormal<o:p></o:p>[/COLOR][/FONT][/SIZE][/COLOR]
[COLOR=#00b050][SIZE=3][FONT=Calibri][COLOR=black]                    .Header = xlYes<o:p></o:p>[/COLOR][/FONT][/SIZE][/COLOR]
[COLOR=#00b050][SIZE=3][FONT=Calibri][COLOR=black]                    .MatchCase = False<o:p></o:p>[/COLOR][/FONT][/SIZE][/COLOR]
[COLOR=#00b050][SIZE=3][FONT=Calibri][COLOR=black]                    .Orientation = xlTopToBottom<o:p></o:p>[/COLOR][/FONT][/SIZE][/COLOR]
[COLOR=#00b050][SIZE=3][FONT=Calibri][COLOR=black]                    .SortMethod = xlPinYin<o:p></o:p>[/COLOR][/FONT][/SIZE][/COLOR]
[COLOR=#00b050][SIZE=3][FONT=Calibri][COLOR=black]                    .Apply<o:p></o:p>[/COLOR][/FONT][/SIZE][/COLOR]
[COLOR=#00b050][SIZE=3][FONT=Calibri][COLOR=black]                  Application.Goto Reference:="R8C1"<o:p></o:p>[/COLOR][/FONT][/SIZE][/COLOR]
[COLOR=#00b050][SIZE=3][FONT=Calibri][COLOR=black]    Rows("8:8").Select<o:p></o:p>[/COLOR][/FONT][/SIZE][/COLOR]
[COLOR=#00b050][SIZE=3][FONT=Calibri][COLOR=black]    Selection.AutoFilter<o:p></o:p>[/COLOR][/FONT][/SIZE][/COLOR]
[COLOR=#00b050][SIZE=3][FONT=Calibri][COLOR=black]                End With<o:p></o:p>[/COLOR][/FONT][/SIZE][/COLOR]
[COLOR=#00b050][SIZE=3][FONT=Calibri][COLOR=black]            End With<o:p></o:p>[/COLOR][/FONT][/SIZE][/COLOR]
[COLOR=#00b050][SIZE=3][FONT=Calibri][COLOR=black]         End With  <o:p></o:p>[/COLOR][/FONT][/SIZE][/COLOR]
[COLOR=#00b050][SIZE=3][FONT=Calibri][COLOR=black]    Next ws<o:p></o:p>[/COLOR][/FONT][/SIZE][/COLOR]
[COLOR=#00b050][SIZE=3][FONT=Calibri][COLOR=black]End Sub<o:p></o:p>[/COLOR][/FONT][/SIZE][/COLOR]

Thanks any help will be greatly appreciated

 

[Skinnerdw]

I can't help you but I can let you know that a few minutes ago I just started a thread on the same subject. I too am being plagued with this error though I am trying to do something different.

 

[Jerry Sullivan]

Hi MZING81,

There are 3 references in your code which are not "fully-qualified" references to the Worksheet Object currently assigned to ws.

Consequently, VBA interprets those as referring to the ActiveSheet instead of ws.

Also, using Selection.AutoFilter to "Toggle" the Autofilters on and off can lead to unexpected results
since it relies on your knowing the state of the AutoFilters before the macro is run.

A reliable alternative is to use .AutoFilterMode = False to clear the AutoFilters.

Here's a revision you could try...

Sub filter()
    Dim ws As Worksheet
    Application.ScreenUpdating = False
    Application.Calculation = xlCalculationManual
    For Each ws In Worksheets
        With ws
            .UsedRange.UnMerge
            .AutoFilterMode = False
            .Rows("8:8").AutoFilter
             With .AutoFilter.Sort
                .SortFields.Clear
                .SortFields.Add Key:=Parent.Range("D8"), _
                    SortOn:=xlSortOnValues, _
                    Order:=xlAscending, _
                    DataOption:= _
                    xlSortNormal
                .Header = xlYes
                .MatchCase = False
                .Orientation = xlTopToBottom
                .SortMethod = xlPinYin
                .Apply
            End With
            .AutoFilterMode = False 'optional to remove filter
         End With
    Next ws
    Application.ScreenUpdating = True
    Application.Calculation = xlCalculationAutomatic
End Sub

I wasn't sure if you wanted to end up with the AutoFilters on or off. Just remove that one line if you want the left on.