Forum Discussion
Get pivot data
If you convert the source data to a table, and use the table range as data source for the pivot table, it should automatically include new rows. But the pivot table will have to be refreshed.
You can do this when the workbook is opened, or when the sheet with the pivot table(s) is activated. Which would you prefer?
You can click in a pivot table, activate the PivotTable Analyze tab, and select Refresh > Refresh All.
If you want this to happen automatically:
Press Alt+F11 to activate the Visual Basic Editor.
Double-click ThisWorkbook in the Project Explorer pane on the left.
Copy the following code into the ThisWorkbook module:
Private Sub Workbook_Open() Me.RefreshAll End SubSwitch back to Excel.
Save the workbook as a macro-enabled workbook (*.xlsm).
Make sure that you allow macros when you open it.
- Can you walk me through converting the source data to a table please?
Click in a cell of the source data.
Activate the Insert tab of the ribbon.
Click Table.
Click OK.
Excel will propose a default name for the table such as Table1. You can accept it or change it to something more meaningful if you like.
Next, click in a pivot table based on these data.
Activate the PivotTable Analyze tab of the ribbon.
Click Change Data Source.
Select the table that you just created. Excel will automatically display the name of the table in the Table/Range box.
Click OK.