Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Automatically Update a Pivot Table Field Used as Column / Row
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Automatically Update a Pivot Table Field Used as Column / Row
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. RDiva;696813 Wrote: 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/sh...d.php?t=194839 http://www.thecodecage.com/forumz |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Automatically Update a Pivot Table Field Used as Column / Row
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.racks.../04_12_10.xlsm Pdf preview (2 pgs): http://www.mediafire.com/file/gmdgdnizmoo/04_12_10.pdf |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
automatically update the pivot table | Excel Discussion (Misc queries) | |||
Pivot table does not automatically update when underlying data cha | New Users to Excel | |||
Automatically Hide Pivot Table Field List Box | Excel Discussion (Misc queries) | |||
Pivot Table update automatically? | Excel Programming | |||
Update Data Field in Pivot Table with Dynamic Excel Range | Excel Programming |