Automatically Update a Pivot Table Field Used as Column / Row

R

RDiva

I have written code which updates several pivot table page fields using a
list. Is there a way to also update a pivot table field when it's used in a
pivot table as a column/row? I tried the code below, however, I get an error
stating that the 'Object doesn't support this property or method'. The only
other option I could think of was to create a second field in the data sheet
which would contain the same data then use this new field as a page field in
the pivot table but I would like to know if there is a way to do this using
VBA.

Sheets("Pivot Tables").Select

ActiveSheet.PivotTables("PivotTable2").PivotItems("Product
Line").CurrentPage = Sheets("Sales").Range("E3").Value
ActiveSheet.PivotTables("PivotTable2").PivotItems("Region").CurrentPage =
Sheets("Sales").Range("B3").Value
 
A

aflatoon

You cannot use CurrentPage for a row/column field. You have to loo
through the PivotItems collection of the PivotField in question and se
the Visible property for each as required.


I have written code which updates several pivot table page fields usin
a
list. Is there a way to also update a pivot table field when it's use in a
pivot table as a column/row? I tried the code below, however, I get a error
stating that the 'Object doesn't support this property or method'. Th only
other option I could think of was to create a second field in the dat sheet
which would contain the same data then use this new field as a pag field in
the pivot table but I would like to know if there is a way to do thi using
VBA.

Sheets("Pivot Tables").Select

ActiveSheet.PivotTables("PivotTable2").PivotItems("Product
Line").CurrentPage = Sheets("Sales").Range("E3").Value
ActiveSheet.PivotTables("PivotTable2").PivotItems("Region").CurrentPag =
Sheets("Sales").Range("B3").Value
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Playing back a Pivot Table macros 2
Pivot Items Filter using VBA 0
delete selection in pivotcolumn 3
Using macro to switch 'page' in a pivot table? 1
Dealing with a Pivot Table (when I don't want to) 0
VBA Pivot Table: Determining item from one field based on anotherfield item 0
Pivot Table Filtering - Macro 3
VBA Pivot Table Error 1

Top