Pivot Items Filter using VBA

[Gaura215]

HelloI have a code which make a Pivot using VBA. however there is a condition that I am unable to fulfilled in that pivot using my VBA. I will try to write it here the code I wish for.ActiveSheet.PivotTables("PivotTable2").PivotFields_ ("ReportTimeframe") .CurrentPage = "(All)" With ActiveSheet.PivotTables("PivotTable2").PivotFields("Report Timeframe") .PivotItems.Last Month.Visible = True .PivotItems.All Other Months.Visible = False End WithI want a fix in my underline codes above.Any help in this would be appreciable.

 

[Domenic]

Maybe...

With ActiveSheet.PivotTables("PivotTable2").PivotFields("ReportTimeframe")    
    .PivotItems("Last Month").Visible = True
    .PivotItems("All Other Months").Visible = False
End With

 

[Gaura215]

HI Domenic

I think I made a mistake in quoting it in the start. I meant, that I have so many dates coming in the filter. I want the macro to automatically select the dates of the previous months only and leave all other month's dates unchecked.

 

[Domenic]

Try...

[FONT=Courier New][COLOR=darkblue]With[/COLOR] ActiveSheet.PivotTables("PivotTable2")

    [COLOR=darkblue]With[/COLOR] .PivotFields("ReportTimeframe")
        .DataRange.Cells(1).Group _
            Start:=True, _
            End:=[COLOR=darkblue]True[/COLOR], _
            Periods:=Array(False, False, False, False, [COLOR=darkblue]True[/COLOR], False, True)
        .Orientation = xlPageField
        .CurrentPage = Format(Date, "mmm")
    [COLOR=darkblue]End[/COLOR] [COLOR=darkblue]With[/COLOR]
    
    [COLOR=darkblue]With[/COLOR] .PivotFields("Years")
        .Orientation = xlPageField
        .CurrentPage = Year(Date)
    [COLOR=darkblue]End[/COLOR] [COLOR=darkblue]With[/COLOR]
    
[COLOR=darkblue]End[/COLOR] [COLOR=darkblue]With[/COLOR]
    [/FONT]

Note, however, if you have a newer version of Excel and the pivot table is not laid out in tabular form, you'll get an error. If this is the case, you can add the following line of code prior to grouping...

[FONT=Courier New]    [COLOR=darkblue]With[/COLOR] ActiveSheet.PivotTables("PivotTable2")    

        .RowAxisLayout xlTabularRow
        
        [COLOR=green]'[/COLOR]
        '
        '

    End With[/FONT]

 

[R Pelle]

This is the code snippet I found on Big Resource and was written by Andrew Poulsom in 2009 who also frequents this forum.
My List:
[TABLE="width: 151"]
<TBODY>[TR]
[TD]Employee
[/TD]
[TD]MyDate
[/TD]
[/TR]
[TR]
[TD]Tom
[/TD]
[TD="align: right"]01/04/2007
[/TD]
[/TR]
[TR]
[TD]Dirk
[/TD]
[TD="align: right"]01/09/2008
[/TD]
[/TR]
[TR]
[TD]Harry
[/TD]
[TD="align: right"]01/07/2009
[/TD]
[/TR]
[TR]
[TD]Jane
[/TD]
[TD="align: right"]01/05/2010
[/TD]
[/TR]
[TR]
[TD]Joe
[/TD]
[TD="align: right"]01/01/2011
[/TD]
[/TR]
[TR]
[TD]Peter
[/TD]
[TD="align: right"]01/02/2011
[/TD]
[/TR]
[TR]
[TD]Rob
[/TD]
[TD="align: right"]01/03/2011
[/TD]
[/TR]
[TR]
[TD]Irfan
[/TD]
[TD="align: right"]01/04/2011
[/TD]
[/TR]
[TR]
[TD]Julia
[/TD]
[TD="align: right"]01/05/2011
[/TD]
[/TR]
[TR]
[TD]Alice
[/TD]
[TD="align: right"]01/06/2011
[/TD]
[/TR]
[TR]
[TD]Indran
[/TD]
[TD="align: right"]01/03/2012
[/TD]
[/TR]
[TR]
[TD]Lee
[/TD]
[TD="align: right"]01/03/2012
[/TD]
[/TR]
</TBODY>[/TABLE]


My Pivot Table, in the report filter I want to see all records that match a date between 1 Jan to 31 Dec 2011.

[TABLE="width: 227"]
<TBODY>[TR]
[TD]MyDate
[/TD]
[TD](Multiple Items)
[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Count of PersNumber
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Employee
[/TD]
[TD]Total
[/TD]
[/TR]
[TR]
[TD]Joe
[/TD]
[TD="align: right"]1
[/TD]
[/TR]
[TR]
[TD]Peter
[/TD]
[TD="align: right"]1
[/TD]
[/TR]
[TR]
[TD]Rob
[/TD]
[TD="align: right"]1
[/TD]
[/TR]
[TR]
[TD]Irfan
[/TD]
[TD="align: right"]1
[/TD]
[/TR]
[TR]
[TD]Julia
[/TD]
[TD="align: right"]1
[/TD]
[/TR]
[TR]
[TD]Alice
[/TD]
[TD="align: right"]1
[/TD]
[/TR]
[TR]
[TD]Grand Total
[/TD]
[TD="align: right"]6
[/TD]
[/TR]
</TBODY>[/TABLE]


The VBA code he wrote:

Sub MultiItemPivotFilter()
    Const StartDate As Date = #1/1/2011#
    Const EndDate As Date = #12/31/2011#
    
    Dim PI As PivotItem
    For Each PI In ActiveSheet.PivotTables("PivotTable1").PivotFields("MyDate").PivotItems
        If DateValue(PI.Name) < StartDate Or DateValue(PI.Name) > EndDate Then
            
Debug.Print DateValue(PI.Name), StartDate, EndDate
            PI.Visible = False
        Else
            PI.Visible = True
        End If
    Next PI
End Sub

Adapt the code to suit your needs. It worked for me.
(Oh and my example list doesnt show the Persnumber column in this post, oops).

 

[Gaura215]

Ok I will try this and let you know if this is working fine or if something more/else is required.

 

[Gaura215]

R Pele

I tried the following code, but I am getting debug error in the highlighted row. Kindly help me out. It did add all dates falling in the month of may, bt it also selected few dates from march and april as well.

Sub MultiItemPivotFilter()
Const StartDate As Date = #5/1/2012#
Const EndDate As Date = #5/31/2012#

Dim PI As PivotItem
For Each PI In ActiveSheet.PivotTables("PivotTable2").PivotFields("Date Logged").PivotItems
If DateValue(PI.Name) < StartDate Or DateValue(PI.Name) > EndDate Then

Debug.Print DateValue(PI.Name), StartDate, EndDate
PI.Visible = False
Else
PI.Visible = True
End If
Next PI
End Sub

 

[R Pelle]

Gaura,
Yes and no.
You can remove the Debug.Print statement, I forgot to remove it, its for testting only.
What I would suggest is you try this code out in a new workbook with a very small set of data, like I did, and step through the code with F8 and hilite the variables to see their values.
As I mentioned, the code came from an MVP and I take no credit for it. I am not a VBA expert at all so I'm not in a position to tell you what else you may try to get the code working other than what I mentioned in the previous sentence. I am assuming this code works in older versions of Excel. I use 2007.

 

[Gaura215]

R Pelle

Still getting the same error

 

[Barasha Biswas]

Instead of

If DateValue(PI.Name) < StartDate Or DateValue(PI.Name) > EndDate Then

use the below line

If PI.Name < CStr(StartDate) Or PI.Name > CStr(EndDate) Then

I have used Cstr function as not all the PI.Name values are date type so converted in string to avoid type mismatch error.
Hope it solves your problem .