xmarine1973
New Member
- Joined
- Sep 11, 2014
- Messages
- 1
I need to create a report heading that says "Includes YTD sales thru xx/xx/xxxx." I would like to write a VBA which dynamically assigns the maximum sales date that was located within a pivot table field (PivotItem list). Notice that I didn't say that I want to change the filter... I only want to use the max date to change the heading at the top of the report.
I've already been through many forums and development websites which explain how to filter the report by the max date. Howevver, I am not looking to alter any of the PivotFields and I'd prefer to leave the filters alone (leave everything set to "All"). Instead, I am only wanting the VBA script to examine one specific field called "activity_date" and then identify the maximum date found. Finally, it should insert this max date into my report heading. Putting all of the dates into the "row labels" is quite messy since there are so many dates in my pivot table so I would prefer not to do this.
For example: Let's say that my pivot table ("PivotTable20") was refreshed today and the "activity_date" filter now ranges between 1/1/2013 to 9/10/2014. I just want the VBA to cycle through all dates to identify the maximum date within that PivotField without altering the filters. After the maximum date has been found, it should update the report heading to say: "Includes YTD sales thru 9/10/2014." When I refresh the report again tomorrow, it should change dynamically to "Includes YTD sales thru 9/11/2014" (since an additional sales day was just recorded).
This sounds so simple. But does anybody have any thoughts on how it could be done?
I've already been through many forums and development websites which explain how to filter the report by the max date. Howevver, I am not looking to alter any of the PivotFields and I'd prefer to leave the filters alone (leave everything set to "All"). Instead, I am only wanting the VBA script to examine one specific field called "activity_date" and then identify the maximum date found. Finally, it should insert this max date into my report heading. Putting all of the dates into the "row labels" is quite messy since there are so many dates in my pivot table so I would prefer not to do this.
For example: Let's say that my pivot table ("PivotTable20") was refreshed today and the "activity_date" filter now ranges between 1/1/2013 to 9/10/2014. I just want the VBA to cycle through all dates to identify the maximum date within that PivotField without altering the filters. After the maximum date has been found, it should update the report heading to say: "Includes YTD sales thru 9/10/2014." When I refresh the report again tomorrow, it should change dynamically to "Includes YTD sales thru 9/11/2014" (since an additional sales day was just recorded).
This sounds so simple. But does anybody have any thoughts on how it could be done?
