Take the 2-minute tour ×
Super User is a question and answer site for computer enthusiasts and power users. It's 100% free, no registration required.

How do I automate the checking/unchecking of dates in 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 into a macro (pasting over the previous 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.

share|improve this question
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Browse other questions tagged or ask your own question.