Home Update Excel Pivot Table Fields
Reply: 1

Update Excel Pivot Table Fields

NewProgrammer
1#
NewProgrammer Published in 2018-02-14 19:05:24Z

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
2#
Excel Sigma Reply to 2018-02-15 05:13:15Z

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.

You need to login account before you can post.

About| Privacy statement| Terms of Service| Advertising| Contact us| Help| Sitemap|
Processed in 0.348454 second(s) , Gzip On .

© 2016 Powered by mzan.com design MATCHINFO