Skip Worksheet in macro

jtodd

Board Regular
Joined
Aug 4, 2014
Messages
157
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
VBA Code:
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 ??
 

Excel Facts

Which came first: VisiCalc or Lotus 1-2-3?
Dan Bricklin and Bob Frankston debuted VisiCalc in 1979 as a Visible Calculator. Lotus 1-2-3 debuted in the early 1980's, from Mitch Kapor.

dmt32

Well-known Member
Joined
Jul 3, 2012
Messages
6,519
Office Version
  1. 2019
Platform
  1. Windows
Hi,
your object variable is named sh you are assigning WS
Change as below & see if solves your problem

Rich (BB code):
If  sh.Name <> "Sheet5"

Dave
 

rollis13

Active Member
Joined
Jul 30, 2012
Messages
426
Office Version
  1. 2016
Platform
  1. Windows
Why don't you use Option Explicit so the compiler would've picked the typo ?
 

jtodd

Board Regular
Joined
Aug 4, 2014
Messages
157
Hi,
your object variable is named sh you are assigning WS
Change as below & see if solves your problem

Rich (BB code):
If  sh.Name <> "Sheet5"

Dave
Thanks for that , should have spotted it - now get the following error
1613475281683.png
 

rollis13

Active Member
Joined
Jul 30, 2012
Messages
426
Office Version
  1. 2016
Platform
  1. Windows

ADVERTISEMENT

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

rollis13

Active Member
Joined
Jul 30, 2012
Messages
426
Office Version
  1. 2016
Platform
  1. Windows
Glad we were able to help(y).
 

Watch MrExcel Video

Forum statistics

Threads
1,130,037
Messages
5,639,673
Members
417,104
Latest member
Nelsini

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top