foolishpiano
New Member
- Joined
- Aug 19, 2016
- Messages
- 25
Hello everyone! I have a macro that tracks the history of items submitted to our system the previous day. This macro saves the data in a table, and I summarize the data with a PivotTable. Management is only interested in items submitted from 3/16 forward, so my PivotTable is filtered to show items submitted 3/16 forward. Everyday I have to add yesterday's date to the filter manually, and I'm just wondering if there is a way to do this through VBA? I tried recording a macro today to see what Excel is doing, and what was recorded was:
What I'm hoping is that I could change the PivotItems field from a hardcoded field to dynamically updating to yesterday's date. Would something like this be possible? I was thinking of referring to a range with yesterday's date in it, such as having cell AI1 = Today()-1, or having the calculation in the VBA code itself; I'm just not sure how it would work.
Any help would be greatly appreciated!
Thanks for reading my post and for your suggestions!
VBA Code:
With ActiveSheet.PivotTables("PivotTable2").PivotFields("CompSubmit")
.PivotItems("4/16/2020").Visible = True
What I'm hoping is that I could change the PivotItems field from a hardcoded field to dynamically updating to yesterday's date. Would something like this be possible? I was thinking of referring to a range with yesterday's date in it, such as having cell AI1 = Today()-1, or having the calculation in the VBA code itself; I'm just not sure how it would work.
Any help would be greatly appreciated!
Thanks for reading my post and for your suggestions!