Home Update Excel Pivot Table Fields
Reply: 0

Update Excel Pivot Table Fields

user1541
1#
user1541 Published in May 26, 2018, 3:48 pm

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.

You need to login account before you can post.

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

© 2016 Powered by mzan.com design MATCHINFO