Hi All,
Ive spent days trying to work out how to get around an issue. Basically I have a formatting macro that then refreshes the pivot tables. The pivot table range moves for some reason so I had to record another macro to fix these. The problem now is I cant save the file anywhere else as it points to a filepath when correcting the pivot table range.
So as you can see if I try and save it anywhere the macro wont work because the sheet is saved somewhere else with the name Report.xlsm Any help will be very much appreciated as my heads wrecked with it.
See code below.
ActiveSheet.PivotTables("PivotTable64").ChangePivotCache ActiveWorkbook. _
PivotCaches.Create(SourceType:=xlDatabase, SourceData:= _
"c:\StaffShared1\Budgets\Prof Services\Management Accounts\Management Acc Reports\40000\[Report.xlsm]YTD!C1:C13" _
, Version:=6)
ActiveSheet.PivotTables("PivotTable65").PivotSelect "Funding", xlButton, True
Range("I7").Select
ActiveSheet.PivotTables("PivotTable65").ChangePivotCache ActiveWorkbook. _
PivotCaches.Create(SourceType:=xlDatabase, SourceData:= _
"c:\StaffShared1\Budgets\Prof Services\Management Accounts\Management Acc Reports\40000\[Report.xlsm]FY!C1:C13" _
, Version:=6)
Ive spent days trying to work out how to get around an issue. Basically I have a formatting macro that then refreshes the pivot tables. The pivot table range moves for some reason so I had to record another macro to fix these. The problem now is I cant save the file anywhere else as it points to a filepath when correcting the pivot table range.
So as you can see if I try and save it anywhere the macro wont work because the sheet is saved somewhere else with the name Report.xlsm Any help will be very much appreciated as my heads wrecked with it.
See code below.
ActiveSheet.PivotTables("PivotTable64").ChangePivotCache ActiveWorkbook. _
PivotCaches.Create(SourceType:=xlDatabase, SourceData:= _
"c:\StaffShared1\Budgets\Prof Services\Management Accounts\Management Acc Reports\40000\[Report.xlsm]YTD!C1:C13" _
, Version:=6)
ActiveSheet.PivotTables("PivotTable65").PivotSelect "Funding", xlButton, True
Range("I7").Select
ActiveSheet.PivotTables("PivotTable65").ChangePivotCache ActiveWorkbook. _
PivotCaches.Create(SourceType:=xlDatabase, SourceData:= _
"c:\StaffShared1\Budgets\Prof Services\Management Accounts\Management Acc Reports\40000\[Report.xlsm]FY!C1:C13" _
, Version:=6)