Macro to Hide/Unhide Pivot Table Items from a list (Excel 2002)

Topic

New Reply

Hello!
I’d like a user to select the month they want to view (the example picks MAR06) from a data validation list I created on the “Input Month” worksheet. Based on the month they pick, I’d like the macro to UNHIDE and then HIDE certain months. I have a few columns to the right of the selected month that have formulas which will show the months that need to be HIDDEN in the pivot table located on the next worksheet. It becomes a bit trickier, because it’s not just one month that needs to remain UNHIDDED, as the pivot table will need to show MAR06 and MAR05, as I would like to show March of 2006 compared to March of 2005. I thought since the pivot table will be used to view any of the months, the macro would need to first UNHIDE all months hidden, and then HIDE the months showing in E5 thru E28 located in the “Input Month” worksheet in my example. Of course, if someone has a better idea that works faster, I’m totally up for it. To complicate it a bit further, I’d like the macro to then do the same for the 2nd pivot table (ALSO located on the “pivot table” worksheet) which will show the Year-to-Date amounts, however this part of the macro would UNHIDE all months hidden, and then HIDE the months showing in F5 thru F28, also located in the “Input Month” worksheet. To top it all off, I plan on having about 15 or more worksheets in the file, each with one or more pivot tables (month and/or both month & ytd pivot tables), of which I’d like the macro to loop through all of them and update the UNHIDE/HIDE functions. Any one with some code out there that may do this already, or have any suggestions, I would greatly appreciate it!!
I’ve attached my example for a reference (please note that I left my current attempts at recording and tweeking the code off of the file as to avoid embarrassment… they didn’t work anyway?!?!?)
Thanks for the help!!!
LJM

Reply | Quote

Replies

It would be best if you gave the pivot tables meaningful names. As it is now, the first pivot table is PivotTable2 and the second one is PivotTable1, which is confusing. With the current setup, you could use the following code:

Public Sub UpdatePivots()
Dim wsi As Worksheet
Dim wsh As Worksheet
Dim pvt As PivotTable
Dim pvf As PivotField
Dim pvi As PivotItem
Dim intMonth As Integer
Dim intMonthSel As Integer
Set wsi = Worksheets(“Input Month”)
intMonth = wsi.Range(“E1”)
For Each wsh In Worksheets
If LCase(Left(wsh.Name, 5)) = “pivot” Then
Set pvt = wsh.PivotTables(2)
Set pvf = pvt.PageFields(“Period”)
For Each pvi In pvf.PivotItems
pvi.Visible = (Application.WorksheetFunction.VLookup _
(pvi.Name, wsi.Range(“J5:K28”), 2, False) = intMonth)
Next pvi
Set pvt = wsh.PivotTables(1)
Set pvf = pvt.PageFields(“Period”)
For Each pvi In pvf.PivotItems
pvi.Visible = (Application.WorksheetFunction.VLookup _
(pvi.Name, wsi.Range(“J5:K28”), 2, False) <= intMonth)
Next pvi
End If
Next wsh
End Sub

This assumes that the names of all worksheets with pivot tables start with "pivot", and that the second pivot table on the sheet is the first item in the PivotTables collection.

Reply | Quote

Thank you Hans! This is exactly what I wanted it to do. It is fabulous… you do great work! I was wondering if some sort of vlookup would work in VBA as I use them all the time in Excel, however I’m still totally green in VBA, so I didn’t know how to go about it until I saw it in the code you wrote for this. This is a great learning tool for me! I really appreciate your help with this!
Thanks again,
LJM

Reply | Quote