Solved

Amend all Pivot Tables in Workbook

Posted on 2011-03-14
2
258 Views
Last Modified: 2012-08-14
Hi Experts!

Please help! I've got a workbook with lots of Pivot Tables, and would like to set the PivotFields and PivotItems across all of them.  I've specified the values in the code below, but I can't get this to run.

Thanks,

Steve
Sub AmendAllPivs()

Dim pt As PivotTable
    
    For Each pt In ActiveSheet.PivotTables

    pt.PivotFields ("Product Name")
        .PivotItems("0").Visible = False
        .PivotItems("DIS01").Visible = False
    pt.PivotFields ("LTV Band")
        .PivotItems("85").Visible = False
        .PivotItems("90").Visible = False
        .PivotItems("95").Visible = False
        .PivotItems("100").Visible = False
        .PivotItems("0").Visible = False
    End With
    Next pt

End Sub

Open in new window

0
Comment
Question by:scsnow2310
2 Comments
 
LVL 85

Accepted Solution

by:
Rory Archibald earned 50 total points
ID: 35129445
You are missing With..End With blocks:
Sub AmendAllPivs()  
  
Dim pt As PivotTable  
      
    For Each pt In ActiveSheet.PivotTables  
  
    With pt.PivotFields ("Product Name")  
        .PivotItems("0").Visible = False  
        .PivotItems("DIS01").Visible = False
    End With
    With pt.PivotFields ("LTV Band")  
        .PivotItems("85").Visible = False  
        .PivotItems("90").Visible = False  
        .PivotItems("95").Visible = False  
        .PivotItems("100").Visible = False  
        .PivotItems("0").Visible = False  
    End With  
    Next pt  
  
End Sub

Open in new window

0
 

Author Closing Comment

by:scsnow2310
ID: 35130483
Many thanks!
0

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Workbook link problems after copying tabs to a new workbook? David Miller (dlmille) Intro Have you either copied sheets to a new workbook, and after having saved and opened that workbook, you find that there are links back to the original sou…
This article will guide you to convert a grid from a picture into Excel format using Microsoft OneNote and no other 3rd party application.
The viewer will learn how to use the =DISCRINV command to create a discrete random variable, use this command to model a set of probabilities and outcomes in a Monte Carlo simulation, and learn how to find the standard deviation of a set of probabil…
This Micro Tutorial will demonstrate the scrolling table in Microsoft Excel using the INDEX function.

895 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now