ChangePivotCache
 

I WANT TO CHANGE THE PIVOT CACHE / DATASOURCE AS PER CUSTOM SELECT RANGE

Original macro code
Changing the PivotCache to new selection

ActiveSheet.PivotTables("Area Performance").ChangePivotCache ActiveWorkbook. _
PivotCaches.Create(SourceType:=xlDatabase, SourceData:= _
"C:\Documents and Settings\amit.madke\Desktop\himangshu\[Laureate
Report Weekly_31-12-07 Orig.xls]Report!R7C1:R918C18" _
, Version:=xlPivotTableVersion10)
ActiveSheet.PivotTables("Area Performance").PivotCache.Refresh

-----------------------
C:\Documents and Settings\amit.madke\Desktop\himangshu\[Laureate Report
Weekly_31-12-07 Orig.xls]Report!R7C1:R918C18

I want change this value dynamically using a variable

i:e
XXXX = "C:\Documents and Settings\amit.madke\Desktop\himangshu\[Laureate
Report Weekly_31-12-07 Orig.xls]Report!R7C1:R918C18"

Implemented in the code as below

ActiveSheet.PivotTables("Area Performance").ChangePivotCache ActiveWorkbook. _
PivotCaches.Create(SourceType:=xlDatabase, SourceData:= _
XXXX _
, Version:=xlPivotTableVersion10)
ActiveSheet.PivotTables("Area Performance").PivotCache.Refresh

But above does not work

It give error
"unable to get the pivottables property of the worksheet class"

Pls suggest solution to this or suggest me right steps of code to do the
above activity

Pls help

Amit