Mega Quick VBA Amendement

[MrMaker]

Afternoon all,

Really quick one.

I have some code (below) that generally works fine (to filter sheets), however, I don't want it to run on a new sheet called 'Summary' and I am getting a debug error.
Can someone add a line to basically say 'ignore Summary sheet' and run only on "Sheet 1". "Sheet 2" and "Sheet 3"

Thank you in advance

Private Sub Workbook_Open()

Dim Sht As Worksheet, R As Range

Application.ScreenUpdating = False

For Each Sht In Me.Worksheets

    If Sht.Name <> "Data Selection" Then

        Set R = Sht.Range("A1").CurrentRegion

        R.AutoFilter field:=1, Criteria1:="<>" & Sheets("Data Selection").Range("A1")

        On Error Resume Next

        Set R = R.Offset(1, 0).SpecialCells(xlCellTypeVisible)

        On Error GoTo 0

        Sht.AutoFilterMode = False

        If Not R Is Nothing Then R.EntireRow.Delete

    End If

    Sht.Rows.RowHeight = 25

Next Sht

With Sheets("Data Selection")

    .Range("A1").Value = .Range("A1").Value

    .Rows.RowHeight = 25

    .Visible = xlHidden

End With

Application.ScreenUpdating = True

End Sub

 

[Fluff]

How about

If Sht.Name <> "Data Selection" And Sht.Name <> "Summary" Then

 

[MrMaker]

Thank you for your quick reply.

I then get a debug error with the following line in yellow

R.AutoFilter field:=1, Criteria1:="<>" & Sheets("Data Selection").Range("A1")

(For context, the cell A1 in 'Data Selection' is the criteria used to select the autofilters

Any ideas?

 

[Fluff]

Do you have any data in & around A1 on the sheet when you get the error?

 

[MrMaker]

It just has a name in it, which then filters the three data tabs.
The filtering happens fine, but then something goes wrong.
I'm a VBA amateur so any help is appreciated

 

[Fluff]

Add this message box as shown

For Each Sht In Me.Worksheets

    If Sht.Name <> "Data Selection" Then

        Set R = Sht.Range("A1").CurrentRegion
        MsgBox Sht.Name & vbLf & R.Address
        R.AutoFilter field:=1, Criteria1:="<>" & Sheets("Data Selection").Range("A1")

What does it say just before you get the error

 

[MrMaker]

It says:

Driver $A$1:$N$1448
Vehicle $A$1:$M$10
Collisions $A$1:$M$7
FAQs $A$1

 

[Fluff]

In that case the problem is with the sheet called FAQs, in that there is no data to filter

 

[MrMaker]

That's right, its just a screenshot of some FAQ's

How can I fix this?

What do the Driver/Vehicle/Collisions references mean? As There is more data in Collisons and Vehicle than that suggests....


Thank you

 

[Fluff]

They are the range of the current region.
If you have more data in those sheets it suggest that you have totally blank rows & or columns.