1 master workbook with multiple separated workbooks on pivot tables and charts

[dellzy]

Hi Excel Experts,

I have this 1 workbook containing multiple macros, a master data worksheet and multiple pivot tables & charts in separate worksheets. I thought it would be easier to refresh all pivot tables and charts whenever there is a change in the master data. However, since the pivot tables and charts will contribute into the whole workbook size, so the more pivot tables and charts I created, the bigger the file size become, and led into slower performance of opening and updating, and in several occasions, some worksheets become just blank or eventually just froze and popped up with errors.

Hence, I have moved all the worksheets of pivot tables and charts into it's own individual workbook but data source still pointing to the master data worksheet in the original file. With this action, everytime the master data has changes, I need to be able to refresh all the pivot tables and charts in those separated workbooks.

Can anyone help to provide a macro that I can run from the master data workbook but will refresh all these pivot tables and charts seamlessly, please? All solutions I've found so far from google search are all about merging them instead of refreshing separately. Perhaps my search phrase is wrong. So I guess this forum maybe the best platform I could explain my exact requirements.

Hopefully someone can contribute to solve my problem as mentioned above.

Thank you in advance.
DZ

 

[Fazza]

hello. do you think it would be sufficient in the workbooks that have pivot tables & charts that each contains VBA as below?
this goes in the workbook's code module, btw

Private Sub Workbook_Activate()    
      ThisWorkbook.RefreshAll
End Sub