Pivot Table parameters in Excel 2007

[y34hright]

Hey does anyone know if there is a way to make pivot table parameters reference a cell and not crash the worksheet. Every time i try to do it it looks like its worked, but when i go back and check the parameter config in the connection properties/definition/parameters the cell reference is blank. and if i refresh the table the worksheet crashes

thanks

[y34hright]

ok... no solutions forthcoming. i need to solve this fairly quick and have a workaound but would be really grateful if someone could help me out with the code as i'm not too clued up on VBA.

all my pivottables use the same 2 parameters: year and month.

so, if i enter year and month into 2 cells on sheet1 and then run a macro which will

- read the values of those two cells into 2 variables called value1 and value2
- modify the appropriate part of the pivotcache.commandtext for every pivot table in my workbook by replacing the sql where clause in every query with the appropriate criteria
values i.e. replace "where year=? and month=?" with "where year=value1 and month=value2"

then the macro simply executes the RefreshAlll command.

Question: how can this bit of code quickly find EVERY pivot table command text without me having to type the full code out for every single one individually. I have about 30 pivot tables spread across about 5 tabs

i have successfully used the following on one pivot table at a time but would like to be able to do a mass type update

#Sub MakePivotCrit()

Dim pc As PivotCache

Set pc = Sheet2.PivotTables(1).PivotCache

pc.CommandText = Replace(pc.CommandText, "'year=?'", "year=2009", , 1)

End Sub
#

that would hopefully do it if anyone can help

thanks