Forum Discussion
Get pivot data
I have a spreadsheet with pivot tables. I want to capture any new case#s as a list in a report. So whenever there is a new report added to my data (I have new defined as within the last 7 days) I want the case number(s) to auto-populate into my report. I am still learning about pivot tables so need the step by step if you can. Thank you
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?
- When I open the workbook I can hit refresh.
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.
