Get pivot data

[LisaMc1021]

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

[#2]

@LisaMc1021 

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?

[#3]

When I open the workbook I can hit refresh.

[#4]

@LisaMc1021 

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 Sub

Switch back to Excel.

Save the workbook as a macro-enabled workbook (*.xlsm).

Make sure that you allow macros when you open it.

[#5]

Can you walk me through converting the source data to a table please?

[#6]

@LisaMc1021 

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.

[#7]

Hi Hans 

I still have the issue of getting all the new reports to list in my report.    How do I get the info from my pivot table to the report?   Using the = formula only populates one cell.