Update Excel Pivot Table Fields

Question

I compile morning reports every day using a workbook with a number of different sheets and pivot tables. So far, I have been able to somewhat automate the data retrieval process, and getting the pivot table data to refresh was very simple. What I would like to do now is get the pivot table field filters to change automatically to include the data entries for thirty days prior up to and including today's date. I have seen some things out there that let you select just one date, but I need to select a range. I have tried using a relatively recorded macro, but it gives me this

Sub Update()

    Sheets("Doorline Month").Select
    ActiveSheet.ChartObjects("Chart 1").Activate
    ActiveSheet.PivotTables(-1).PivotFields("Date").CurrentPage = "(All)"
    With ActiveChart.PivotLayout.PivotTable.PivotFields("Date")
        .PivotItems("1/14/2018").Visible = False
        .PivotItems("2/15/2018").Visible = True
    End With
End Sub

I believe that this would only deselect 1/14/2018 and make 2/15/18 visible. I want this code to work automatically every morning, so if I come in on 2/16/2018 it deselects 1/15/2018 and selects 2/16/2018. I have tried replacing the dates in the recorded macro with "ActiveCell" but to no avail. Any help is greatly appreciated. If it helps, the data for my pivot tables is in sheet 1 and the dates specifically are in column A. Unfortunately, the number of data entries per day is not consistent so I don't know if that complicates things or not.

Excel Sigma Excel Sigma · Accepted Answer · 2018-02-15T05:13:15

Why dont you put a flag in the database/ data of origin that marks with 1 or 0 each line depending if the date is within the range you want or not? Then you can leave the 1 or the 0 selected in the pivot table's filter field and the relevant data would show automatically every time you refresh.

Update Excel Pivot Table Fields

1 Answers