G
Guest
I use the following macro in several spreadsheets, to automate the
spreadsheet and make it easier for end users. It loops through and updates
all data tables, then loops through and updates all the pivots tables.
The problem is, one file that I use this on, has seven different pivot
tables, that are all linked together. When I created the 2nd through seventh
pivot table, I told the pivot table wizard that the data source was the 1st
pivot table.
As you know, when they are linked in this way, you only have to refresh one
of the pivot tables, and they all refresh. With this macro though, it
refreshes the first one, which refreshes all of them, then goes to the second
pivot and refreshes all seven again, and so on, and so on.
Is there any way for the macro to only refresh the original, and not refresh
all the ones that are linked to it? As it is now, it takes longer than I
would like for the macro to finish.
Sub Auto_Open()
'
' Auto_Open Macro
'
Dim ws As Worksheet
Dim qt As QueryTable
Dim pt As PivotTable
For Each ws In ThisWorkbook.Worksheets
For Each qt In ws.QueryTables
qt.Refresh False
Next qt
Next ws
For Each ws In ThisWorkbook.Worksheets
For Each pt In ws.PivotTables
pt.RefreshTable
Next pt
Next ws
End Sub
spreadsheet and make it easier for end users. It loops through and updates
all data tables, then loops through and updates all the pivots tables.
The problem is, one file that I use this on, has seven different pivot
tables, that are all linked together. When I created the 2nd through seventh
pivot table, I told the pivot table wizard that the data source was the 1st
pivot table.
As you know, when they are linked in this way, you only have to refresh one
of the pivot tables, and they all refresh. With this macro though, it
refreshes the first one, which refreshes all of them, then goes to the second
pivot and refreshes all seven again, and so on, and so on.
Is there any way for the macro to only refresh the original, and not refresh
all the ones that are linked to it? As it is now, it takes longer than I
would like for the macro to finish.
Sub Auto_Open()
'
' Auto_Open Macro
'
Dim ws As Worksheet
Dim qt As QueryTable
Dim pt As PivotTable
For Each ws In ThisWorkbook.Worksheets
For Each qt In ws.QueryTables
qt.Refresh False
Next qt
Next ws
For Each ws In ThisWorkbook.Worksheets
For Each pt In ws.PivotTables
pt.RefreshTable
Next pt
Next ws
End Sub