Prev: ? offset('activecell',,-2,1,1) in Named Formula ?
Next: save as version 2003 problem
From: RDiva on 12 Apr 2010 09:17
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

--
RDiva
From: aflatoon on 12 Apr 2010 10:11

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



R
D
i
v
a
;
6
9
6
8
1
3

W
r
o
t
e
:


>
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
>
> --
> RDiva


--
aflatoon

Regards,
A.
------------------------------------------------------------------------
aflatoon's Profile: 1501
View this thread: http://www.thecodecage.com/forumz/showthread.php?t=194839

http://www.thecodecage.com/forumz

From: Herbert Seidenberg on 12 Apr 2010 19:24
Excel 2007 PivotTable
Update multiple PTs, multiple sheets.
Row/Column/Page Fields.
With Multiple Items in Page Field(s)
Matches selections of one chosen PT to all.
http://c0718892.cdn.cloudfiles.rackspacecloud.com/04_12_10.xlsm
Pdf preview (2 pgs):
http://www.mediafire.com/file/gmdgdnizmoo/04_12_10.pdf
 | 
Pages: 1
Prev: ? offset('activecell',,-2,1,1) in Named Formula ?
Next: save as version 2003 problem