Changing date ranges in pivot table

Question

How do I automate the checking/unchecking of dates in the report filtre of a pivot table?

I have a pivot table from which I need to extract data based on a date range (last seven days, previous seven days). The spreadsheet has multiple pivot tables and the process of clicking the myriad checkboxes (year, quarter, month, each day, uncheck previous days, resizing and scrolling within the dropdown) for each of them is maddening.

Pivot Table Checkboxes

I'd like to have a field with a date in it, and then run a macro that revises the selected dates based on that date. e.g. Enter "11/11" in cell A1, and have only the checkboxes for the date range of November 4-10 be checked for some tables and the date range of October 28-November 3 checked for others. Then next week, I could update A1 with a new date and go from there.

I have been able to write a macro that sets the date checkboxes but I can't update it without manually typing in the actual days of the month. Below is the chunk of code that manipulates the date checkboxes. But how could I automate the whole endeavour? Ideally, any solution would take into account that some weeks span two months.

ActiveSheet.PivotTables(ptNameA20).TableRange1.Select
ActiveSheet.PivotTables(ptNameA20).PivotFields( _
    "[Date].[Date Hierarchy].[Year]").VisibleItemsList = Array("")
ActiveSheet.PivotTables(ptNameA20).PivotFields( _
    "[Date].[Date Hierarchy].[Quarter]").VisibleItemsList = Array("")
ActiveSheet.PivotTables(ptNameA20).PivotFields( _
    "[Date].[Date Hierarchy].[Month]").VisibleItemsList = Array("")
ActiveSheet.PivotTables(ptNameA20).PivotFields( _
    "[Date].[Date Hierarchy].[Day]").VisibleItemsList = Array( _
    "[Date].[Date Hierarchy].[Day].&[2013-11-04T00:00:00]", _
    "[Date].[Date Hierarchy].[Day].&[2013-11-05T00:00:00]", _
    "[Date].[Date Hierarchy].[Day].&[2013-11-06T00:00:00]", _
    "[Date].[Date Hierarchy].[Day].&[2013-11-07T00:00:00]", _
    "[Date].[Date Hierarchy].[Day].&[2013-11-08T00:00:00]", _
    "[Date].[Date Hierarchy].[Day].&[2013-11-09T00:00:00]", _
    "[Date].[Date Hierarchy].[Day].&[2013-11-10T00:00:00]")`

I've tried using loops to generate strings such as "2013-11-04", "2013-11-05", etc... and then building a long string (encompassing the 'day' lines). However, I'm unable to then use the result except by pasting them into a macro (pasting over the previous week's block of text). This results in a lot of copying and pasting while worrying about overwriting the wrong pivot table. This is better than manually checking and unchecking boxes but still not good.

I'm using Excel 2010. N.B. I asked this in SuperUser but haven't received any responses.

I need the data to be daily (i.e. to generate a graph that shows data for each day of the week). Additionally, grouping by week isn't available in the report filtre. As I understand it, it's only available in the row labels. — mockman

mockman mockman · Accepted Answer · 2013-11-22T21:45:40

While I am unable to get any insight into a VBA solution to this problem, I did find an alternative that should be just as quick to use (and quicker to implement).

Slicers

By adding two slicers (one for each of the last two weeks), I am able to select the date range with a click-drag. One added to the sheet, they can be connected to multiple pivot tables, even spread over multiple worksheets. They can also be colour-formatted to match the pivot tables that they connect to.

Note: If anyone does have any recommendations as to a VBA solution, I would still appreciated seeing them.

From Microsoft's site: Use slicers to filter PivotTable data

Changing date ranges in pivot table

1 Answers