Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
macro refinement
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
macro refinement
Untested...
Thisworkbook.refreshall Maybe it'll just do everything once???? Jonathan Cooper wrote: 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 -- Dave Peterson |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
macro refinement
This seemed to updated the data table, but didn't touch the pivot tables.
Any other ideas? "Dave Peterson" wrote: Untested... Thisworkbook.refreshall Maybe it'll just do everything once???? Jonathan Cooper wrote: 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 -- Dave Peterson |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Macro Help Needed - Excel 2007 - Print Macro with Auto Sort | Excel Worksheet Functions | |||
League Ladder refinement of Sorting. | Excel Programming | |||
ROBBYN - RE -Code Refinement involving blank cell(s) COULD NOT POST FOLLOWUP | Excel Programming | |||
Code Refinement involving blank cell(s) | Excel Programming | |||
Start Macro / Stop Macro / Restart Macro | Excel Programming |