Refresh Excel pivot table data range with VBA rather than Pivot Table Wizard?

Question

I would like to somehow write a piece of VBA code that would detect a change in the Pivot Table data range and automatically updates the range without having to go through the Pivot Table Wizard. Could someone show me the VBA Code?

Answer 1

I am sure this is possible, but in case no one answers, here is a workaround

Create everything. Change the data. Go to Tools>Macro>Record new macro. When you enter your macro name you can assign it a shortcut key. I would choose r (Lowercase (Refresh)). Go through the process of refreshing the data. When you are done Go to Tools>Macro>Stop recording.

Go Back and change a bunch of data and then hit the Ctrl+r key. Should work.

Answer 2

You don't have to use the Pivot Table Wizard. Just right click on any of the data in the Pivot Table and select Refresh Data from the shortcut menu.



As far as the code here's what I'd do to create some VBA code that will update the pivot table each time you activate the worksheet it is on:

First right click on the data in the Pivot table and select Table Options. In the Pivot Table Options window that pops up write down the name of the pivot table that shows up in the name textbox. You'll need that name later. Then close the window.

Press Alt + F11, this will open Visual Basic Editor. Then in the Project Window on the left hand side look for your workbook and select the name of the worksheet that your Pivot table is on. The tab name of the worksheet will be in parenthesis. Double click on that worksheet name and a white window should pop up in the main window. Above that white window will be two dropdown boxes. Select Worksheet from the left dropdown and select Activate from the right dropdown. The following code should appear:

Private Sub Worksheet_Activate()

End Sub

Between the two lines of code paste the following.

ActiveSheet.PivotTable _
("PivotTableName") _
.PivotCache.Refresh

Now replace PivotTableName with the name of your pivot table that you wrote down. Make sure you don't do away with the quotation marks.

The space then underscore represents a line continuation in VB programming. So normally the ( would start right after the e on the same line.

Click the save button in Visual Basic Editor then you can close the window.

Now Excel will update the Pivot table each time that you activate that worksheet by selecting that worksheet.

PS - Another way of getting that code is to record a macro while you refresh the data by using the right click method that I mentioned. Then you can go back and view the macro code by clicking the Edit button instead of running the macro. However you need to insert that code into the worksheet section with that Worksheet and Activate selected like I mentioned so that it updates when the worksheet is activated.