Macro to update Pivot Table data

crazyracer

New Member
Joined
Feb 20, 2009
Messages
2
Hi guys, this is my 1st post and i know it won't be my last.

I have a pivot table that feeds off a data sheet and that data sheet gets updated w/ new information each month. I have a few pivot tables that is linked to this sheet. i tried to create a macro that will automatically update the pivot table range to include the newly added data in that data sheet. however, it doesn't work. can anyone help? thanks a mil
 

Excel Facts

Last used cell?
Press Ctrl+End to move to what Excel thinks is the last used cell.

Derek Brown

Well-known Member
Joined
Dec 26, 2005
Messages
2,390
Hello and welcome to The Board.
For this example I am assuming that your data table starts in Cell A1.
Select all the data (including the Headings) in your table and then give it a 'name'.
You give the table a name by entering the name (without spaces) in the Name box that you see above Column A - after entering the name, you must press Enter.
Now we need to change the reference of that 'name' - use the menu option Insert | Name | Define and select our name of 'Dataset'.
Now change the formula in the 'RefersTo' box to the following:
=OFFSET(Sheet1!$A$1,0,0,COUNTA(Sheet1!$A:$A),COUNTA(Sheet1!$1:$1))
[If your data is not on Sheet1, change the formula accordingly but note that if the sheet name contains spaces you must put a single quote around it - for example: 'Sheet One'!$A$1)
You can test the effect of this by using Edit | GoTo and then typing 'Dataset' in the Reference box and pressing Enter - your data table will be selected if you have entered the formula correctly.
Try adding another column and row of data and using Edit | Goto again.
Now select a cell in your Pivot Table, right mouse-click, select PivotTable Wizard and then press the 'Back' button once. Change the formula in 'Range' to "Dataset" (without the quotes) - press Finish.
The above will automatically update the data range associated with the Pivot Table but you still need to 'refresh' the pivot table.
You would normally do this by selecting a cell in the pivot table, right mouse-clicking and choosing 'Refresh'.
To do this automatically you have to add the following code to the Workbook module.
Use Alt + F11 to bring up the VBE window, double-click 'ThisWorkbook" in the Explorer pane.
In the window to the right of the Explorer pane enter:
Code:
Private Sub Workbook_SheetActivate(ByVal Sh As Object)
    On Error Resume Next
    ActiveSheet.Cells(4, 1).Select
    ActiveSheet.PivotTables(1).PivotCache.Refresh
    On Error GoTo 0
End Sub
In the example given. Cells(4,1) is a cell within the pivot table - make sure that you do not select a cell outside the pivot table.
Now, as long as macros are enabled when the workbook is opened, selecting a sheet containing a pivot table will result in the pivot table being refreshed.
Note that the code assumes that you only have one pivot table on a worksheet.
 

crazyracer

New Member
Joined
Feb 20, 2009
Messages
2
Thank you so much for your help. I will try this when I get to work 1st thing tomorrow. Thanks again. Much appreciated.
 

Forum statistics

Threads
1,141,405
Messages
5,706,264
Members
421,437
Latest member
GijoeBlack

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top