Pivot Table Filter Options Help needed!

[becca51178]

This is driving me insane.

Ok.

I have a workbook with several different pivot tables that I use for a report. I inherited the workbook so the tables are a bit sporadic.

A pivot table field that is for the current period is named "FinPeriod"

A pivot table field for Year to Date is "FinPeriod2". The periods represent months and filter by numbers 1-12, 1=January, 2=February etc..

I have this code below that I can filter out for the current period from an input box

Dim ws As Worksheet
    Dim pt As PivotTable
    Dim pi As PivotItem
    Dim strDate As String
     
     
 strDate = Application.InputBox(Prompt:="Date to filter by")
    
   On Error Resume Next
    For Each ws In Worksheets
        For Each pt In ws.PivotTables
            pt.ManualUpdate = False
             For Each pi In pt.PivotFields("FinPeriod").PivotItems
                pi.Visible = pi.Value = strDate
            Next pi
         pt.ManualUpdate = True
       Next pt
    Next ws
  On Error GoTo 0

Now, I need to set the parameters to show the whole workbook and I need to figure out a way to show YTD filter criteria.

When I enter the values for "FinPeriod2", the YTD filters box will have checked the values 1-5 to show January-May data.

I have tried everything I can think of to make it work and I really need help!

Thanks!
Rebecca

 

[Jerry Sullivan]

Hi Rebecca,

What area of the PivotTable Report is the field "FinPeriod" placed (Report Filters, Row Labels, or Column Labels)?

 

[becca51178]

[/URL][/IMG]





Rather then trying to poorly explain what it looks like I inluded a snapshot of each.

I have figured out how to changed it to just the current period (left screen shot), but can't get it to change to the current period and all previous ones.

It's driving me mad!

any help would be great, Thanks!

 

[wizz]

Hi,
I found a solution for YTD values.
add this code to yours:

On Error Resume Next
For Each ws In Worksheets
For Each pt In ws.PivotTables
pt.ManualUpdate = False

'here I deselect all values exept first one
pt.PivotFields("FinPeriod2").PivotItems(1).Visible = True
For i = 2 To pt.PivotFields("FinPeriod2").PivotItems.Count
Set pi = pt.PivotFields("FinPeriod2").PivotItems(i)
pi.Visible = False
Next i

'here I select all values less or equal to the respective period (YTD)
For Each pi In pt.PivotFields("FinPeriod2").PivotItems
pt.PivotFields("FinPeriod2").EnableMultiplePageItems = True
If pi.Value <= CInt(strDate) Then
pi.Visible = True
End If
Next pi
pt.ManualUpdate = True
Next pt
Next ws
On Error GoTo 0


Good luck!

 

[becca51178]

It worked!

Your code is awesome!

Thank you so much!