VBA to Filter Pivot Table based on Incremental Date Variable

[KDavidP1987]

Hello all,

I have been working on a script to automatically filter data in two pivot tables using the date input by the user in a named cell. Was trying to do this using a while loop, but I don't know if that's possible or the best way to handle it.

I need the filter on the pivot table to start at the date entered, and then include data for each date forward (to today). It also needs to include one other item within the date field that is not a date (No Data), indicating the ticket wasn't closed (no date exists).

Here is the VBA I have scripted so far:


-------------------------------------------------------------------------------------------------

Sub AdjustPivots2()​

'​

' Adjusts filters for pivot tables to show only tickets closed on or after the reporting date
' Or tickets that're still open, shown as (no data)​

'​

​

Dim SDate As Date​

SDate = [RStartDate]

'SDate is variable date. Set to be the start date of the report, based on user entry
'The date is later incremented in while loop to increase date until it reaches date()​

​

Sheets("Pivot_Incidents-Closed").Select​

ActiveSheet.PivotTables("PivotTable5").ClearAllFilters
'Clears filter from 1st Pivot table​

​

Sheets("Pivot_Aging Report").Select​

ActiveSheet.PivotTables("PivotTable6").ClearAllFilters
'Clears filter from second pivot table​

​

Sheets("Pivot_Incidents-Closed").Select​

ActiveSheet.PivotTables("PivotTable5").PivotFields("Date Closed").CurrentPage _​

= "(no data)"
'Sets filter on first pivot table to where only (No Data) values are shown (tickets still open)​

​

Do While SDate <= Date​

On Error GoTo Invalid​

​

With ActiveSheet.PivotTables("PivotTable5").PivotFields("Date Closed")​

.PivotItems(SDate).Visible = True​

​

End With​

​

Invalid:​

​

SDate = SDate + 1​

​

Loop​

​

​

End Sub​

-------------------------------------------------------------------------------------------------


Here is what I know so far:

• The variable works just fine, confirmed with tracker while stepping into script
• The loop works just fine with the date, it increments it until it stops
• There was an error occurring for any missing dates, thus I had to enter the OnError code to bypass
• On testing (Stepping into):
  • The clear pre-existing pivot filters code works just fine
  • The assign (No Data) to filter works just fine
  • When it reaches the while loop it goes through it, but DOES NOT enabled the dates of the variable

I think the issue is with this, .PivotItems(SDate).Visible = True, but I don't know how to correct it! It just reads right over it without turning the date on in the filter.

Any guidance would be most appreciated!

Sincerely,

Kristopher




****** id="cke_pastebin" style="position: absolute; top: 0px; width: 1px; height: 1px; overflow: hidden; left: -1000px;">-------------------------------------------------------------------------------------------------</body>

 

[ranman256]

in the pivot design, grab the cell with the date,
drop it into the FILTERS box in right pivot fields.

pick your date off the pivot table filter.

 

[KDavidP1987]

Apologies, but this solution doesn't address what I'm asking. I need to filter multiple pivot tables automatically using a script that pulls from a date value entered into a cell on a dashboard type screen. The script should adjust the Pivot Tables to show data from that date forward (until current date is reached), along with the segment of data showing (no data).

I have a date variable in my script that updates itself with the user inputted cells value, and have it set to increment each time the while loop repeats. However, I need to call this date from within the while loop to show that date within the pivot table

Otherwise, if there is another way (within VBA) to accomplish this outside of using a while loop I would be willing to try