Skip Worksheet in macro

[jtodd]

I have the following code to update all my pivots from one cell ,
Problem i now have is that i do not want to update sheet 5

Sub UpdatePivottables()
    Dim sh As Worksheet
    Dim pt As PivotTable
    Dim ptField As PivotField
    Dim ptItem As PivotItem
    Dim pc As PivotCache
    For Each sh In ThisWorkbook.Worksheets
    [U]If WS.Name <> "Sheet5" Then[/U]
        For Each pt In sh.PivotTables
            pt.PivotFields("Week").ClearAllFilters
            For Each ptItem In pt.PivotFields("Week").PivotItems
                If ptItem.Name <> ActiveSheet.Range("C6").Value Then
                    ptItem.Visible = False
                End If
            Next ptItem
        Next pt
   Next sh
End Sub

When i run this code i get an error - if i comment out the uderlined it works fine .
What am i doing wrong ??

 

[dmt32]

Hi,
your object variable is named sh you are assigning WS
Change as below & see if solves your problem

If  sh.Name <> "Sheet5"

Dave

 

[rollis13]

Why don't you use Option Explicit so the compiler would've picked the typo ?

 

[jtodd]

Hi,
your object variable is named sh you are assigning WS
Change as below & see if solves your problem

If  sh.Name <> "Sheet5"

Dave

Thanks for that , should have spotted it - now get the following error

 

[rollis13]

Yes indeed, now you are missing a End If before Next sh.

 

[jtodd]

That works fine now , thanks for your patience

 

[rollis13]

Glad we were able to help.