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
|
