Select items in Page fields in Pivot table using VBA

ericj

New Member
Joined
Mar 16, 2011
Messages
5
Hello
Using Excel 2010

I have these two pieces of code below that put 2 fields into the page area of a pivot table.
I want to choose 2 out of 40 items in OrderID
I want to choose 1 out of 20 items in CustomerID

Is this possible without having to write true vs false code for each item in VBA?

Any alternative code greatly appreciated.

'Insert Page Fields
With ActiveSheet.PivotTables("SalesPivotTable").PivotFields("OrderID")
.Orientation = xlPageField
.Position = 1
End With

With ActiveSheet.PivotTables("SalesPivotTable").PivotFields("CustomerID")
.Orientation = xlPageField
.Position = 2
End With

Thank you
Eric
 

Some videos you may like

Excel Facts

Links? Where??
If Excel says you have links but you can't find them, go to Formulas, Name Manager. Look for old links to dead workbooks & delete.

RoryA

MrExcel MVP, Moderator
Joined
May 2, 2008
Messages
34,802
Office Version
365, 2019, 2016, 2010
Platform
Windows, MacOS
For CustomerID you can just set the currentpage to the value you want. For the other one, you'll have to loop.
 

ericj

New Member
Joined
Mar 16, 2011
Messages
5
For CustomerID you can just set the currentpage to the value you want. For the other one, you'll have to loop.
Thank you.
Could you provide me the actual VBA code how to select just one item and then also how to do a loop?

Thank you
Eric
 

RoryA

MrExcel MVP, Moderator
Joined
May 2, 2008
Messages
34,802
Office Version
365, 2019, 2016, 2010
Platform
Windows, MacOS
For a single value:

Code:
ActiveSheet.PivotTables("SalesPivotTable").PivotFields("CustomerID").currentpage = "some value"
For the loop:

Code:
Dim pi as pivotitem
with ActiveSheet.PivotTables("SalesPivotTable").PivotFields("OrderID")
.clearallfilters
for each pi in .pivotitems
if pi.name = "item 1" or pi.name = "Item2" then
pi.visible = true
else
pi.visible = false
end if
next pio
end with
 

Watch MrExcel Video

Forum statistics

Threads
1,095,726
Messages
5,446,152
Members
405,386
Latest member
xcookiemonster64

This Week's Hot Topics

Top