becca51178
Board Regular
- Joined
- Feb 19, 2012
- Messages
- 64
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
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
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
Code:
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