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?

Wuggles
  • 1

2 Answers2

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.

Scott - Слава Україні
  • 22,532
user1036677
  • 1
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.

Pynner
  • 401
  • 2
  • 6