VBA filter to a Set PivotItem

[MrsLisa]

Hello. I've learned how to create a pivot table using the pivot cache. My next step is to use vba to filter the table to 1 specific item. See the attached (I want to set the location filter to New York. Do you of know a good tutorial that covers this? Thank you.

 

[Nebu]

Hi:

I added the following line to your Module 1 addCache subroutine
.CurrentPage = "New York"

Option Explicit

Sub addCache()
ThisWorkbook.PivotCaches.Add _
(SourceType:=xlDatabase, _
SourceData:=Range("A1").CurrentRegion).CreatePivotTable _
TableDestination:="R4C" & Range("A1").CurrentRegion.Columns.Count + 3

With ActiveSheet.PivotTables(1)
'First row field
With .PivotFields("Name")
.Orientation = xlRowField
.Position = 1
End With

'Report Filter field
With .PivotFields("Location")
.Orientation = xlPageField
.Position = 1
.CurrentPage = "New York"
End With

'Order Amount or numerical data  in the Values field
.AddDataField ActiveSheet.PivotTables(1).PivotFields("Order Amount"), _
"Sum of Amount", xlSum
End With


End Sub

Thanks

 

[NARAYANK991]

Hi ,

See if this link is helpful :

http://www.globaliconnect.com/excel...-values-a-dates-using-vba&catid=79&Itemid=475

Narayan

 

[MrsLisa]

Wonderful! Thank you very much!