Results 1 to 3 of 3
  1. #1
    3 Star Lounger
    Join Date
    Jul 2002
    Location
    Ankeny, Iowa, USA
    Posts
    344
    Thanks
    8
    Thanked 0 Times in 0 Posts

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

    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

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

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

    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.

  3. #3
    3 Star Lounger
    Join Date
    Jul 2002
    Location
    Ankeny, Iowa, USA
    Posts
    344
    Thanks
    8
    Thanked 0 Times in 0 Posts

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

    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

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •