Updating Pivot Table

[paulasky220]

I would like to create a macro where once the master data worksheet is updated with a new year, the pivot table will update this information.. example 1Q07 below will become 1Q08.
Have no idea if this is possible...

ActiveSheet.PivotTables("PivotTable101").PivotFields("Date").ClearAllFilters
ActiveSheet.PivotTables("PivotTable101").PivotFields("Date").CurrentPage = _
"1Q07"
Range("D1").Select

 

[SkipVought]

Hi,

What happens if you REFRESH your table.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[paulasky220]

Basically, I have 3 years worth of data by quarters..2007-2009, I deleted 2007 and replaced info with 2010 and when I refresh my pivot tables 2008 info is populated in the other spreadsheet. The pivot tables are my source of info where the information is being pulled from for another summary sheet and I was wondering if there was a way to update it.

I have 3 years worth of data in the spreadsheet and it's as follows:
1Q07(pivot) 1Q08(pivot 2) 1Q09 (pivot3)
when I refresh the pivot after I delete 2007 and add 2010 then the first pivot table picks up all info for 2008, but the rest of the pivot tables remain as 2008 and 2009 so I was trying to figure out a way to be able to update this automatically without having to go in there and check and change each pivot table.

 

[SkipVought]

That makes absolutely no sense at all.

When your source data changes and you refresh your PT, it reflects the changed data in your source data.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[Gavona]

IF (and I am guessing) you have three pivot tables but these are in fact identical except for the page field (year) value 1Q07, 1Q08 etc. then there will be a way.

First option is to use just one pivot and then use the ShowPages method. However I find that can, in certain conditions result in annoying messing up of column widths.

Second option is to identify all the possible values in the Year column (maybe use Advanced filter to create a unique list). Then step through the list creating the pivot tables by copying the sheet containing the master and altering the value of the page field.


Gavin

 

[paulasky220]

what's the showpages method?

 

[PHV]

what's the showpages method?
No F2 nor F1 key on your keyboard ?

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886