Dashboard Automation

1Option Explicit
2 
3' Dashboard Automation — refresh every PivotTable + chart on the
4' "Dashboard" sheet and export a snapshot PNG for the daily report.
5 
6Public Sub RefreshDashboard()
7    Dim wb As Workbook, ws As Worksheet
8    Dim pt As PivotTable, ch As ChartObject
9    Dim snapshotPath As String
10 
11    On Error GoTo Fail
12    Set wb = ThisWorkbook
13    Application.ScreenUpdating = False
14    Application.Calculation = xlCalculationManual
15 
16    wb.RefreshAll
17    DoEvents
18 
19    For Each ws In wb.Worksheets
20        For Each pt In ws.PivotTables
21            pt.RefreshTable
22        Next pt
23    Next ws
24 
25    Set ws = wb.Worksheets("Dashboard")
26    For Each ch In ws.ChartObjects
27        ch.Chart.Refresh
28    Next ch
29 
30    snapshotPath = wb.Path & "\Snapshots\" & Format(Now, "yyyymmdd_hhmm") & ".png"
31    ExportDashboardPNG ws, snapshotPath
32 
33    Application.Calculation = xlCalculationAutomatic
34    Application.ScreenUpdating = True
35    MsgBox "Dashboard refreshed. Snapshot: " & snapshotPath, vbInformation
36    Exit Sub
37Fail:
38    Application.Calculation = xlCalculationAutomatic
39    Application.ScreenUpdating = True
40    MsgBox "Refresh failed: " & Err.Description, vbCritical
41End Sub