ThrottleWorks
Excel Ninja
I have one macro file. In this file I have sheet named as ‘Piovt_Samples’.
There are 15 different pivot tables saved in this worksheet.
This worksheet act as a reference sheet for me.
For each pivot table I need to derive some data from a different file, process the data and pass it as range of the particular pivot table.
For example, in ‘Piovt_Samples’ worksheet, Pivot Table 1, data is derived from ‘Yamaha’ file, processed, Copy required data, go to ‘Output file’ add a worksheet, paste the data here, assign this data as range to Pivot Table 1.
Close ‘Yamaha’ file without saving.
Now go to ‘Piovt_Samples’ file, copy pivot table 1, paste it in ‘Output File’.
Save ‘Output file’.
This way, I process and assign range for 15 different pivots
Now my problem is, when I re-open ‘Output file’ once macro is done and double click on any of the pivot table.
The pivot table does not get expand, I need to refresh the file before doing it.
Is there any way to avoid re-refreshing the entire file manually.
Can anyone please help me in this.
I get below mentioned message when double clicked on pivot table.
The PivotTable report was saved without the underlying data. Use the Refresh Data command to updated the report.
There are 15 different pivot tables saved in this worksheet.
This worksheet act as a reference sheet for me.
For each pivot table I need to derive some data from a different file, process the data and pass it as range of the particular pivot table.
For example, in ‘Piovt_Samples’ worksheet, Pivot Table 1, data is derived from ‘Yamaha’ file, processed, Copy required data, go to ‘Output file’ add a worksheet, paste the data here, assign this data as range to Pivot Table 1.
Close ‘Yamaha’ file without saving.
Now go to ‘Piovt_Samples’ file, copy pivot table 1, paste it in ‘Output File’.
Save ‘Output file’.
This way, I process and assign range for 15 different pivots
Now my problem is, when I re-open ‘Output file’ once macro is done and double click on any of the pivot table.
The pivot table does not get expand, I need to refresh the file before doing it.
Is there any way to avoid re-refreshing the entire file manually.
Can anyone please help me in this.
I get below mentioned message when double clicked on pivot table.
The PivotTable report was saved without the underlying data. Use the Refresh Data command to updated the report.
Code:
Set TempRng = ActiveSheet.Range("A1").CurrentRegion
TempRng.SpecialCells(xlCellTypeVisible).Copy
TempBook.Activate
Worksheets.Add
ActiveSheet.Paste
'Enter in Pivot Table Name
PivotName = "PivotTable1"
'Set range for pivot table
Set PvtRng = ActiveSheet.Range("A1").CurrentRegion
'Change range of pivot table
ThisWorkbook.Activate
Worksheets("All_Pivots").PivotTables("PivotTable1").ChangePivotCache ActiveWorkbook. _
PivotCaches.Create(SourceType:=xlDatabase, SourceData:=PvtRng, Version:=xlPivotTableVersion10)
'Ensure Pivot Table is Refreshed
AllPvtSht.PivotTables(PivotName).RefreshTable
AllPvtSht.Select
TempLr = TempBook.Worksheets(“YoYo”).Cells(TempBook.Worksheets(“YoYo”).Rows.Count, 1).End(xlUp).Row + 2
'Copy Pivot Table Header
AllPvtSht.Range("A6").Copy
TempBook.Worksheets(“YoYo”).Cells(TempLr, 1).PasteSpecial xlPasteValues
TempBook.Worksheets(“YoYo”).Cells(TempLr, 1).PasteSpecial xlPasteFormats
Range("S2").Select
ActiveSheet.PivotTables(“PivotTable1”).PivotSelect "", xlDataAndLabel, True
Selection.Copy
TempLr = TempBook.Worksheets(“YoYo”).Cells(TempBook.Worksheets(“YoYo”).Rows.Count, 1).End(xlUp).Row + 3
TempBook.Worksheets(“YoYo”).Cells(TempLr, 1).PasteSpecial
TempBook.Activate
ActiveWorkbook.RefreshAll
TempBook.Save
