0

I have about 50 Pivot Tables in Excel 2007 that need a drop down updated on a daily basis several times to reflect multiple pieces of data from the page I have of data. It is very time consuming to update every table over and over to recover these charts. Is there a way to update them in one place, maybe reference a cell instead of updating every drop down?

2 Answers 2

0

Unfortunately I don't think excel has a built in way to update all pivot tables.

I would achieve this by recording a macro of updating each pivot table, then linking this macro to a button. if that is unfamiliar territory for you, let me know and I can elaborate further.

2
  • I am not familiar with this in Pivot Tables, but I understand where you are going with this. Can I actually update drop downs in the macro? What if I want to update the same drop down multiple times to different values? What I mean is, I have about 50 tables that represent data in different ways, I want to select different airlines and represent all that data for each airline. Is that possible? Would I need a different macro for each airline for all 50 tables? Thanks for your help!
    – Wuggles
    Oct 14, 2011 at 14:18
  • The path of least resistance here really depends on how many airlines and how much data. recording a macro will record your selections so you would need record a macro for each airline. To have single macro update all tables based on an input selection will require some hand made vba code. Depending on the nature of the data you are collecting it may be easier to hit the data behind the pivot table directly with formulas (like vlookup, sumif and sumproduct)
    – Pynner
    Oct 14, 2011 at 16:02
0

I needed this recently and modified it a bit.  Perhaps someone will find it useful.

I have a few pivot charts summarizing data by Calendar Week for the year to date. I recorded a macro [Office 2016: View-Macros-Record Macro] and checked the last CW to be included in the row labels for all the individual pivot tables and thus the charts. I then stopped the macro and entered edit mode (Alt+F11) and located the code in Module 1.

I then set cell C2 in the worksheet to always be the week number of last week [=WEEKNUM(Today())-1] , set a variable equal to that cell value and replaced the selection in the recorded macro with the variable name using the following code:

Dim i As Integer

Sub CW_Update()

' Keyboard Shortcut: Ctrl+Shift+J

i = Cells(1, 3).Value

    With ActiveSheet.PivotTables("PivotTable1").PivotFields("CW")
        .PivotItems(i).Visible = True
    End With
    With ActiveSheet.PivotTables("PivotTable2").PivotFields("CW")
        .PivotItems(i).Visible = True
    End With
    With ActiveSheet.PivotTables("PivotTable3").PivotFields("CW")
        .PivotItems(i).Visible = True
    End With
    With ActiveSheet.PivotTables("PivotTable4").PivotFields("CW")
        .PivotItems(i).Visible = True
    End With
    With ActiveSheet.PivotTables("PivotTable5").PivotFields("CW")
        .PivotItems(i).Visible = True
    End With
    With ActiveSheet.PivotTables("PivotTable6").PivotFields("CW")
        .PivotItems(i).Visible = True
    End With
        With ActiveSheet.PivotTables("PivotTable7").PivotFields("CW")
        .PivotItems(i).Visible = True
    End With

End Sub

Now, every Monday when I review the stats I just press Ctrl+Shift+J and everything updates automatically.

1

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .