Can I update multiple Excel 2007 Pivot Tables data drop down one time?

by Wuggles   Last Updated May 15, 2019 18:01 PM

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?



Answers 2


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.

Pynner
Pynner
October 14, 2011 13:04 PM

I needed this recently and modified it a bit. Perhaps someone finds 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

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.

Hope someone can use it.

user1036677
user1036677
May 15, 2019 17:13 PM

Related Questions


Smileys in Outlook, how to prevent it

Updated March 19, 2017 00:01 AM


Can't open Email Attachments file in Microsoft office

Updated November 13, 2017 23:01 PM

Outlook 2007 script error

Updated March 26, 2017 06:01 AM