• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

VBA to update/refresh Charts in a workbook.

raf

New Member
Hi Guys, I recorded a macro just to refresh the charts. My file consists of 5 sheets with different charts on every sheets. below code is perfectly working but im looking forward to simplify it and avoid visible loading progress. Hope you can help me.
thank you in advance.


Code:
Sub Macro2() 
  '
  ' Macro2 Macro
  '
  '
  Sheets("CCP").Select 
  ActiveSheet.ChartObjects("Chart 18").Activate 
  ActiveChart.PivotLayout.PivotTable.PivotCache.Refresh 
  ActiveSheet.ChartObjects("Chart 6").Activate 
  ActiveChart.PivotLayout.PivotTable.PivotCache.Refresh 
  ActiveSheet.ChartObjects("Chart 9").Activate 
  ActiveChart.PivotLayout.PivotTable.PivotCache.Refresh 
  ActiveSheet.ChartObjects("Chart 14").Activate 
  ActiveChart.PivotLayout.PivotTable.PivotCache.Refresh 
  ActiveSheet.ChartObjects("Chart 15").Activate 
  ActiveChart.PivotLayout.PivotTable.PivotCache.Refresh 
  ActiveSheet.ChartObjects("Chart 16").Activate 
  ActiveChart.PivotLayout.PivotTable.PivotCache.Refresh 
  ActiveSheet.ChartObjects("Chart 17").Activate 
  ActiveChart.PivotLayout.PivotTable.PivotCache.Refresh 
  
  ''''''''''''''''''''''''''''''''''''
  ''''''''''''''''''''''''''''''''''''
  ''''''''''''''''''''''''''''''''''''
  
  Sheets("PCC").Select 
  ActiveSheet.ChartObjects("Chart 17").Activate 
  ActiveChart.PivotLayout.PivotTable.PivotCache.Refresh 
  ActiveSheet.ChartObjects("Chart 18").Activate 
  ActiveChart.PivotLayout.PivotTable.PivotCache.Refresh 
  ActiveSheet.ChartObjects("Chart 21").Activate 
  ActiveChart.PivotLayout.PivotTable.PivotCache.Refresh 
  ActiveSheet.ChartObjects("Chart 22").Activate 
  ActiveChart.PivotLayout.PivotTable.PivotCache.Refresh 
  ActiveSheet.ChartObjects("Chart 24").Activate 
  ActiveChart.PivotLayout.PivotTable.PivotCache.Refresh 
  ActiveSheet.ChartObjects("Chart 26").Activate 
  ActiveChart.PivotLayout.PivotTable.PivotCache.Refresh 
  
  ''''''''''''''''''''''''''''''''''''
  ''''''''''''''''''''''''''''''''''''
  ''''''''''''''''''''''''''''''''''''
  
  Sheets("TCP").Select 
  ActiveSheet.ChartObjects("Chart 30").Activate 
  ActiveChart.PivotLayout.PivotTable.PivotCache.Refresh 
  ActiveSheet.ChartObjects("Chart 31").Activate 
  ActiveChart.PivotLayout.PivotTable.PivotCache.Refresh 
  ActiveSheet.ChartObjects("Chart 34").Activate 
  ActiveChart.PivotLayout.PivotTable.PivotCache.Refresh 
  ActiveSheet.ChartObjects("Chart 35").Activate 
  ActiveChart.PivotLayout.PivotTable.PivotCache.Refresh 
  ActiveSheet.ChartObjects("Chart 36").Activate 
  ActiveChart.PivotLayout.PivotTable.PivotCache.Refresh 
  ActiveSheet.ChartObjects("Chart 38").Activate 
  ActiveChart.PivotLayout.PivotTable.PivotCache.Refresh 
  
  ''''''''''''''''''''''''''''''''''''
  ''''''''''''''''''''''''''''''''''''
  ''''''''''''''''''''''''''''''''''''
  
  Sheets("PST").Select 
  ActiveSheet.ChartObjects("Chart 18").Activate 
  ActiveChart.PivotLayout.PivotTable.PivotCache.Refresh 
  ActiveSheet.ChartObjects("Chart 21").Activate 
  ActiveChart.PivotLayout.PivotTable.PivotCache.Refresh 
  ActiveSheet.ChartObjects("Chart 22").Activate 
  ActiveChart.PivotLayout.PivotTable.PivotCache.Refresh 
  ActiveSheet.ChartObjects("Chart 23").Activate 
  ActiveChart.PivotLayout.PivotTable.PivotCache.Refresh 
  ActiveSheet.ChartObjects("Chart 24").Activate 
  ActiveChart.PivotLayout.PivotTable.PivotCache.Refresh 
  ActiveSheet.ChartObjects("Chart 25").Activate 
  ActiveChart.PivotLayout.PivotTable.PivotCache.Refresh 
  ''''''''''''''''''''''''''''''''''''
  ''''''''''''''''''''''''''''''''''''
  ''''''''''''''''''''''''''''''''''''
  Sheets("MPA").Select 
  ActiveSheet.ChartObjects("Chart 18").Activate 
  ActiveChart.PivotLayout.PivotTable.PivotCache.Refresh 
  ActiveSheet.ChartObjects("Chart 21").Activate 
  ActiveChart.PivotLayout.PivotTable.PivotCache.Refresh 
  ActiveSheet.ChartObjects("Chart 22").Activate 
  ActiveChart.PivotLayout.PivotTable.PivotCache.Refresh 
  ActiveSheet.ChartObjects("Chart 23").Activate 
  ActiveChart.PivotLayout.PivotTable.PivotCache.Refresh 
  ActiveSheet.ChartObjects("Chart 24").Activate 
  ActiveChart.PivotLayout.PivotTable.PivotCache.Refresh 
  ActiveSheet.ChartObjects("Chart 25").Activate 
  ActiveChart.PivotLayout.PivotTable.PivotCache.Refresh 
  MsgBox "File has been refreshed, Charts have been updated" 
End Sub
 
This should do the trick.
Code:
Sub ChartUpdate()
Dim mSheet As Worksheet, mPivot As PivotTable
For Each mSheet In ActiveWorkbook.Worksheets
    For Each mPivot In mSheet.PivotTables
        mPivot.RefreshTable
        mPivot.Update
    Next
Next
MsgBox "File has been refreshed, Charts have been updated"
End Sub
 
  • Like
Reactions: raf
This should do the trick.
Code:
Sub ChartUpdate()
Dim mSheet As Worksheet, mPivot As PivotTable
For Each mSheet In ActiveWorkbook.Worksheets
    For Each mPivot In mSheet.PivotTables
        mPivot.RefreshTable
        mPivot.Update
    Next
Next
MsgBox "File has been refreshed, Charts have been updated"
End Sub


This is what im looking for, thank you so much!
 
Hi, can someone help me in recording a macro to capture the data to the chart from sheet1 in the same workbook.There is about 10 charts in one sheet.

Hope someone can help me.

Thanks in Advance!
 
Back
Top