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

How to find 2nd largest value in a column?
MAX finds the largest value. =LARGE(A:A,2) will find the second largest. =SMALL(A:A,3) will find the third smallest

dmt32

Well-known Member
Joined
Jul 3, 2012
Messages
6,512
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
423
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
423
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
423
Office Version
  1. 2016
Platform
  1. Windows
Glad we were able to help(y).
 

Watch MrExcel Video

Forum statistics

Threads
1,129,515
Messages
5,636,793
Members
416,941
Latest member
shazzaxyz

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