Pivot Table Filter Options Help needed!

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



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
 

Excel Facts

Can a formula spear through sheets?
Use =SUM(January:December!E7) to sum E7 on all of the sheets from January through December

Jerry Sullivan

MrExcel MVP
Joined
Mar 18, 2010
Messages
8,787
Hi Rebecca,

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

becca51178

Board Regular
Joined
Feb 19, 2012
Messages
64
becca511
[/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

New Member
Joined
May 13, 2010
Messages
17
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!
 

Forum statistics

Threads
1,136,794
Messages
5,677,778
Members
419,719
Latest member
evo2411

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top