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.
