Forum Discussion

LisaMc1021's avatar
LisaMc1021
Copper Contributor
Oct 17, 2023

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 ...
Formulas and Functions
HansVogelaar's avatar
HansVogelaar
MVP
Oct 17, 2023

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?

  • LisaMc1021's avatar
    LisaMc1021
    Copper Contributor
    Oct 17, 2023
    When I open the workbook I can hit refresh.
    • HansVogelaar's avatar
      HansVogelaar
      MVP
      Oct 17, 2023

      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.

      • LisaMc1021's avatar
        LisaMc1021
        Copper Contributor
        Oct 17, 2023
        Can you walk me through converting the source data to a table please?

Resources