Simple VBA Amendment

[MrMaker]

Hello all,

A really simple one for you folk I'm sure.

I've got some existing code which I need to adapt ever so slightly.

1) The code currently runs on all sheets except 'Summary', but I need it to run on all sheets except 'Summary' and 'FAQs'

2) The other tabs have an autofilter on row 4 (which is the basis of the code), but I'm not sure this code is picking up that the code isn't on the top row, do I need to change something to reflect that.

Private Sub Workbook_Open()

Dim Sht As Worksheet, R As Range

Application.ScreenUpdating = False

For Each Sht In Me.Worksheets

If Sht.Name <> "Summary" Then

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

R.AutoFilter field:=1, Criteria1:="<>" & Sheets("Summary").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("Summary")

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

.Rows.RowHeight = 20

End With

Application.ScreenUpdating = True

End Sub

Thank you in advance

MrMaker

 

[Fluff]

How about

   If Sht.Name <> "Summary" And Sht.Name <> "FAQs" Then

Also when posting code please post as text with the code tags, not as a table. Thanks

 

[MrMaker]

Thank you, and sorry!

Any idea on having the autofilter on row 4?

 

[Fluff]

How about

   For Each Sht In Me.Worksheets
      If Sht.Name <> "Summary" And Sht.Name <> "FAQs" Then
         Sht.Range("A4").AutoFilter 1, "<>" & Sheets("Summary").Range("A1")
         Sht.AutoFilter.Range.Offset(1).EntireRow.Delete
         Sht.AutoFilterMode = False
      End If
      Sht.Rows.RowHeight = 25
   Next Sht