Hi Tina,
Do you want the Pivot Table to refresh after every refresh of the query?
Is the query already set up to refresh as soon as the workbook opens?
The process is simple. First, when you create the PivotTable, DO NOT assign the range for the PivotTable data using cell addresses. INSTEAD, select the range you want to use for the PivotTable data, then use (Excel menu bar) Insert >> Name >> Define. Name it something like "pivotData". NOW, when you create the PivotTable, instead of entering cell address, enter pivotData (no quotes).
If the Pivot Table is already set up (I assume it is), you can change this by using the Pivot Wizard and going "back" to the screen that askes for the data range; enter pivotData on that screen, then click "Finish".
OK ... now that the Pivot Table is ready for easy updating ... go to the "other view" of Excel. Press Alt+F11. Locate the subroutine name "SAPBEXonRefresh".
If you are unfamiliar with this "other view" of Excel, read some of my earlier postings. Or, post back to this thread and I can send you additional instructions for locating this subroutine.
In the subroutine named SAPBEXonRefresh, you can insert this VB code:
resultArea.Name = "pivotData"
For Each ws In ThisWorkbook.Worksheets
For Each pvt In ws.PivotTables
pvt.RefreshTable
Next pvt
Next ws
This will make your pivot table update every time that your query updates. (Not necessarily every time the workbook opens.)
If you have more than one query or more than one pivot table in the workbook, it would be a little bit different.
Hope this helps. Post again if you have additional questions.
- Pete
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.