Macro to change date (in rowfield of pivot)

[wouter.vanmeert]

Hi all


I'm trying to create a form control in which a user can select a beginning month and an ending month(for example beginning date is january 2010, end date is february 2011).


The input from these two fields should be linked to a macro that changes the date (in the row fields) of multiple pivot tables (these pivots are linked to pivot charts, changing the inputfields will change the timeframe of these graphs).


I've found this on Peltierblog (the names in VBA of the pivot fields), but can't seem to find any other info on how to manipulate pivot tables using macros.

> http://peltiertech.com/WordPress/referencing-pivot-table-ranges-in-vba/


Any help would be great!

 

[Luke M]

If you record a macro of yourself changing the PivotField, that should give you the basis of the code you need. Then, you can just stick that code in some type of loop like:

[pre]

For Each PT in ActiveSheet.PivotCaches
'New code goes here
Next PT

[/pre]

 

[wouter.vanmeert]

Hi Luke!


I tried the following and it works, but only for one of my pivots (so I'm kind of stuck with the loop you told me about):


I created a table with three columns:

Column 1: date (FY10 period 1 = 1001, FY10 period 2 = 1002, etc.)

Column 2: List Number

Column 3: True/False


I put 2 dropdown list elements in my dashboard. In this dropdown list, a From and- ToPeriod can be selected. Using IF-formula's I make sure that in my table there's TRUE of FALSE behind every Date.


Then I gave each cell in the True/False column a name (FilterDate1001, FilterDate1002, etc.)


In my macro (which is linked to the two dropdown lists so it runs each time a user changes a period), I use the following code:


'Sheets("PIVOTS").Select

ActiveSheet.PivotTables("PvtAmount").PivotFields("M3PERIOD").CurrentPage = _

"(All)"


With ActiveSheet.PivotTables("PvtAmount").PivotFields("M3PERIOD")

.PivotItems("1001").Visible = [FilterDate1001]

.PivotItems("1002").Visible = [FilterDate1002]

.PivotItems("1003").Visible = [FilterDate1003]

.PivotItems("1004").Visible = [FilterDate1004]

.PivotItems("1005").Visible = [FilterDate1005]

.PivotItems("1006").Visible = [FilterDate1006]

.PivotItems("1007").Visible = [FilterDate1007]

.PivotItems("1008").Visible = [FilterDate1008]

.PivotItems("1009").Visible = [FilterDate1009]

.PivotItems("1010").Visible = [FilterDate1010]

.PivotItems("1011").Visible = [FilterDate1011]

.PivotItems("1012").Visible = [FilterDate1012]


.PivotItems("1101").Visible = [FilterDate1101]

.PivotItems("1102").Visible = [FilterDate1102]

.PivotItems("1103").Visible = [FilterDate1103]

.PivotItems("1104").Visible = [FilterDate1104]

.PivotItems("1105").Visible = [FilterDate1105]

.PivotItems("1106").Visible = [FilterDate1106]

.PivotItems("1107").Visible = [FilterDate1107]

.PivotItems("1108").Visible = [FilterDate1108]

.PivotItems("1109").Visible = [FilterDate1109]

.PivotItems("1110").Visible = [FilterDate1110]

.PivotItems("1111").Visible = [FilterDate1111]

.PivotItems("1112").Visible = [FilterDate1112]


.PivotItems("1201").Visible = [FilterDate1201]

.PivotItems("1202").Visible = [FilterDate1202]

.PivotItems("1203").Visible = [FilterDate1203]

.PivotItems("1204").Visible = [FilterDate1204]

.PivotItems("1205").Visible = [FilterDate1205]

.PivotItems("1206").Visible = [FilterDate1206]

.PivotItems("1207").Visible = [FilterDate1207]

.PivotItems("1208").Visible = [FilterDate1208]

.PivotItems("1209").Visible = [FilterDate1209]

.PivotItems("1210").Visible = [FilterDate1210]

.PivotItems("1211").Visible = [FilterDate1211]

.PivotItems("1212").Visible = [FilterDate1212]


End With


ActiveSheet.PivotTables("PvtAmount").PivotFields("M3PERIOD"). _

EnableMultiplePageItems = True


End Sub'


As you can see, this code is real basic and probably quite slow as well.


My questions:

1. How do I implement a loop so that all pivots in my Pivot Table sheet are updated?

2. Can I put in a range for all the cells in the True/False column (thus creating a shorter code and probably faster one?).


Thanks for the help!