You've built Excel dashboards before. You know the drill: wire up some PivotTables, add a few slicers, format everything until it looks respectable, and hand it off to the business. Then the data changes. Someone adds a new region. A sales rep asks why the chart doesn't reflect last week's numbers. The VP wants a "refresh button." And suddenly your clean dashboard is a maintenance nightmare that requires you personally to touch it every Monday morning.
The difference between a dashboard that looks dynamic and one that is dynamic comes down to automation. When VBA is woven into the fabric of your workbook — responding to slicer selections, recalculating KPI tiles on demand, rebuilding chart series when source data grows — the dashboard stops being a static artifact and starts behaving like a lightweight application. That's what we're building here.
By the end of this lesson, you'll have a fully automated Excel dashboard that refreshes its data connection, programmatically controls slicers, calculates and displays KPI summaries, and updates charts without you lifting a finger. We're going to build this around a realistic sales performance scenario — regional revenue tracking across a 12-month period — so the code you write here maps directly onto the kind of work you'd do in a real business context.
What you'll learn:
SlicerCache objectsGETPIVOTDATA-style logic in VBARefresh procedure triggered by a button or workbook eventYou should be comfortable with:
ListObject), structured referencesYou do not need to be a VBA expert. We'll explain every non-obvious piece of code as we go.
Before writing a single line of VBA, get your workbook structure right. A dashboard that's hard to maintain is usually one where data, calculations, and presentation got mixed together in the same sheet. We're going to enforce a three-layer architecture:
Layer 1 — Data (tbl_Sales): A single Excel Table on a sheet called DATA. This is the only place raw data lives. It has columns: Date, Region, Rep, Product, Revenue, Units, Target.
Layer 2 — Calculation (PIVOT): A hidden sheet containing your PivotTables. PivotTables are the calculation engine. They're fast, they handle aggregation cleanly, and VBA can interact with them through a well-documented object model. Hiding this sheet keeps the user experience clean.
Layer 3 — Presentation (DASHBOARD): The sheet users actually see. It contains your KPI tiles (just formatted cells with values written by VBA), your PivotChart, and the slicer controls. No raw data here. No PivotTables visible to the user.
To set this up:
DATA, PIVOT, and DASHBOARD.DATA, paste or import your sales records and format them as a Table (Insert > Table) named tbl_Sales.PIVOT, create a PivotTable from tbl_Sales. Name it pvt_SalesSummary — you'll reference it by name in VBA, so this matters. Add Region to Rows, Date (grouped by Month) to Columns, and Revenue and Units to Values. Add a second PivotTable named pvt_RepPerformance with Rep in Rows and Revenue in Values.DASHBOARD, add three KPI tile areas (just merged or bordered cell ranges) for Total Revenue, Revenue vs. Target %, and Top Region. Leave a large area for the chart. Add slicers from your PivotTable for Region and Product — Insert > Slicer from the PivotTable tab.PIVOT sheet tab and select Hide.Your DASHBOARD sheet now has slicers connected to PivotTables on a hidden sheet. The KPI cells are empty. The chart doesn't exist yet. That's fine — VBA is going to fill all of this in.
KPI tiles are where most dashboard VBA goes wrong. The typical mistake is hardcoding cell references that break when the PivotTable layout shifts. Instead, we're going to read values directly from the PivotTable object.
Open the VBA Editor (Alt+F11). Insert a new module and name it mod_KPI.
Option Explicit
' -------------------------------------------------------
' Constants for sheet and object names
' -------------------------------------------------------
Private Const PIVOT_SHEET As String = "PIVOT"
Private Const DASH_SHEET As String = "DASHBOARD"
Private Const PT_SUMMARY As String = "pvt_SalesSummary"
Private Const DATA_TABLE As String = "tbl_Sales"
' -------------------------------------------------------
' UpdateKPIs
' Calculates and writes the three KPI tiles to DASHBOARD.
' -------------------------------------------------------
Public Sub UpdateKPIs()
Dim wsDash As Worksheet
Dim wsPivot As Worksheet
Dim pt As PivotTable
Dim wsData As Worksheet
Dim totalRevenue As Double
Dim totalTarget As Double
Dim vsTargetPct As Double
Dim topRegion As String
Dim topRegionRev As Double
Dim pf As PivotField
Dim pi As PivotItem
Set wsDash = ThisWorkbook.Sheets(DASH_SHEET)
Set wsPivot = ThisWorkbook.Sheets(PIVOT_SHEET)
Set pt = wsPivot.PivotTables(PT_SUMMARY)
Set wsData = ThisWorkbook.Sheets("DATA")
' --- Total Revenue: sum the Grand Total from the PivotTable ---
' The Grand Total row is the last data row in the PivotTable's
' DataBodyRange. We grab the column that corresponds to Revenue.
On Error GoTo KPI_Error
totalRevenue = pt.GetPivotData("Revenue")
' --- Revenue vs Target: calculated from the raw table ---
' We use WorksheetFunction to aggregate the structured table.
' This respects any slicer filters because slicers filter the
' PivotTable cache, not the raw table -- so we calculate target
' from the visible pivot data instead.
Dim targetField As PivotField
' Check if Target is in the pivot; if not, calculate from raw data
totalTarget = WorksheetFunction.Sum( _
wsData.ListObjects(DATA_TABLE).ListColumns("Target").DataBodyRange)
If totalTarget > 0 Then
vsTargetPct = (totalRevenue / totalTarget) * 100
Else
vsTargetPct = 0
End If
' --- Top Region: iterate PivotItems to find max Revenue ---
topRegionRev = 0
topRegion = "N/A"
Set pf = pt.PivotFields("Region")
For Each pi In pf.PivotItems
If pi.Visible Then
Dim regionRev As Double
On Error Resume Next
regionRev = pt.GetPivotData("Revenue", "Region", pi.Value)
On Error GoTo KPI_Error
If regionRev > topRegionRev Then
topRegionRev = regionRev
topRegion = pi.Value
End If
End If
Next pi
' --- Write to DASHBOARD KPI tiles ---
' We reference named ranges for the tile cells so layout changes
' don't break the code.
wsDash.Range("kpi_TotalRevenue").Value = totalRevenue
wsDash.Range("kpi_VsTarget").Value = vsTargetPct / 100
wsDash.Range("kpi_TopRegion").Value = topRegion & " ($" & _
Format(topRegionRev, "#,##0") & ")"
Exit Sub
KPI_Error:
MsgBox "KPI calculation error: " & Err.Description, vbExclamation, "Dashboard Error"
End Sub
A few things worth understanding here:
pt.GetPivotData("Revenue") is the programmatic equivalent of the GETPIVOTDATA worksheet function. When called with only the data field name, it returns the grand total. This is robust — it doesn't depend on which cell the grand total happens to occupy.
Named ranges for KPI cells (kpi_TotalRevenue, kpi_VsTarget, kpi_TopRegion) are defined on the DASHBOARD sheet via Formulas > Name Manager. This decouples your VBA from the physical cell layout. If you move a tile, you update the named range, not the code.
The slicer limitation on totalTarget is something practitioners trip over constantly. Slicers filter the PivotTable cache — they do not filter the underlying ListObject. So WorksheetFunction.Sum on the raw table column ignores slicer state. For this KPI, that's actually correct behavior (we want to show actuals vs. full-year target), but you need to be conscious of this distinction everywhere.
Tip: If you want a KPI that respects slicer filters, calculate it from the PivotTable object, not from the raw
ListObject. The PivotTable reflects the filtered cache; the table does not.
Slicers have their own VBA object model, and it's genuinely useful. The SlicerCache object lives at the workbook level and controls which items are selected. You can read slicer state, set it, and respond to changes.
Add a new module called mod_Slicers.
Option Explicit
' -------------------------------------------------------
' SelectAllSlicerItems
' Clears all filter selections on a named slicer cache,
' effectively selecting "All". Useful for a Reset button.
' -------------------------------------------------------
Public Sub SelectAllSlicerItems(ByVal slicerCacheName As String)
Dim sc As SlicerCache
Dim si As SlicerItem
On Error GoTo Slicer_Error
Set sc = ThisWorkbook.SlicerCaches(slicerCacheName)
' Turning off MultiSelect first prevents partial-selection errors
sc.ClearManualFilter
Exit Sub
Slicer_Error:
MsgBox "Slicer error on '" & slicerCacheName & "': " & Err.Description, _
vbExclamation, "Slicer Control"
End Sub
' -------------------------------------------------------
' SelectSlicerItems
' Selects only the specified items on a slicer cache.
' itemList is a comma-delimited string: "North,South"
' -------------------------------------------------------
Public Sub SelectSlicerItems(ByVal slicerCacheName As String, _
ByVal itemList As String)
Dim sc As SlicerCache
Dim si As SlicerItem
Dim items() As String
Dim i As Long
Dim targetDict As Object ' Scripting.Dictionary for O(1) lookup
Set targetDict = CreateObject("Scripting.Dictionary")
items = Split(itemList, ",")
For i = 0 To UBound(items)
targetDict(Trim(items(i))) = True
Next i
Set sc = ThisWorkbook.SlicerCaches(slicerCacheName)
' *** Critical pattern: you cannot deselect the last visible item. ***
' Strategy: first select ALL items, then deselect what we don't want.
sc.ClearManualFilter
Application.ScreenUpdating = False
For Each si In sc.SlicerItems
If targetDict.exists(si.Name) Then
si.Selected = True
Else
si.Selected = False
End If
Next si
Application.ScreenUpdating = True
Exit Sub
End Sub
' -------------------------------------------------------
' GetSelectedSlicerItems
' Returns a comma-delimited string of currently selected
' slicer items. Useful for logging or conditional logic.
' -------------------------------------------------------
Public Function GetSelectedSlicerItems(ByVal slicerCacheName As String) As String
Dim sc As SlicerCache
Dim si As SlicerItem
Dim result As String
Set sc = ThisWorkbook.SlicerCaches(slicerCacheName)
For Each si In sc.SlicerItems
If si.Selected Then
result = result & si.Name & ","
End If
Next si
If Len(result) > 0 Then result = Left(result, Len(result) - 1)
GetSelectedSlicerItems = result
End Function
To find your slicer cache names, go to the VBA Immediate Window and type:
For Each sc In ThisWorkbook.SlicerCaches : Debug.Print sc.Name : Next sc
Slicer cache names are typically something like Slicer_Region or Slicer_Product1. They're set when the slicer is created and can be renamed in the slicer's settings (right-click > Slicer Settings).
Warning: The pattern of iterating
SlicerItemsand toggling.Selectedhas a gotcha: Excel throws an error if you try to deselect the last remaining selected item. TheClearManualFiltercall at the top ofSelectSlicerItemsensures everything is selected before you start toggling, which avoids this condition entirely.
PivotCharts are convenient because they inherit their data from the PivotTable automatically. But standard charts connected directly to table data require manual range management — and that's where things get interesting with VBA.
We'll use a PivotChart here (connected to pvt_SalesSummary), but we'll also write a routine that handles the common case of a standalone chart whose source range grows as new data arrives. This is the pattern you'll use most often in production.
Add a module called mod_Charts.
Option Explicit
' -------------------------------------------------------
' SetupDashboardChart
' Creates (or resets) the main PivotChart on DASHBOARD.
' Run once during dashboard initialization.
' -------------------------------------------------------
Public Sub SetupDashboardChart()
Dim wsDash As Worksheet
Dim wsPivot As Worksheet
Dim pt As PivotTable
Dim co As ChartObject
Dim cht As Chart
Set wsDash = ThisWorkbook.Sheets("DASHBOARD")
Set wsPivot = ThisWorkbook.Sheets("PIVOT")
Set pt = wsPivot.PivotTables("pvt_SalesSummary")
' Remove existing chart if present
Dim existingCO As ChartObject
For Each existingCO In wsDash.ChartObjects
If existingCO.Name = "cht_RevenueByRegion" Then
existingCO.Delete
Exit For
End If
Next existingCO
' Create chart in DASHBOARD at a fixed position
' Coordinates: Left, Top, Width, Height (in points)
Set co = wsDash.ChartObjects.Add(Left:=20, Top:=160, _
Width:=580, Height:=300)
co.Name = "cht_RevenueByRegion"
Set cht = co.Chart
' Connect chart to the PivotTable
cht.SetSourceData Source:=pt.TableRange1
' Clustered column chart, clean formatting
cht.ChartType = xlColumnClustered
cht.HasTitle = True
cht.ChartTitle.Text = "Monthly Revenue by Region"
' Style the plot area
With cht.PlotArea
.Interior.Color = RGB(248, 248, 248)
.Border.LineStyle = xlNone
End With
' Format the series colors to match corporate palette
Dim seriesColors As Variant
seriesColors = Array(RGB(31, 119, 180), RGB(255, 127, 14), _
RGB(44, 160, 44), RGB(214, 39, 40))
Dim s As Long
For s = 1 To cht.SeriesCollection.Count
If s <= UBound(seriesColors) + 1 Then
cht.SeriesCollection(s).Interior.Color = seriesColors(s - 1)
End If
Next s
' Remove gridlines for a cleaner look
On Error Resume Next
cht.Axes(xlValue).MajorGridlines.Delete
On Error GoTo 0
' Add subtle horizontal gridlines back
cht.Axes(xlValue).HasMajorGridlines = True
cht.Axes(xlValue).MajorGridlines.Format.Line.ForeColor.RGB = RGB(220, 220, 220)
End Sub
' -------------------------------------------------------
' UpdateStandaloneChart
' For charts NOT connected to a PivotTable: resizes the
' source data range to match the current table extent.
' Use this when your chart reads directly from tbl_Sales
' or a summary range that changes size.
' -------------------------------------------------------
Public Sub UpdateStandaloneChart(ByVal chartName As String, _
ByVal sourceSheet As String, _
ByVal sourceRangeName As String)
Dim wsDash As Worksheet
Dim wsSrc As Worksheet
Dim co As ChartObject
Dim srcRng As Range
Set wsDash = ThisWorkbook.Sheets("DASHBOARD")
Set wsSrc = ThisWorkbook.Sheets(sourceSheet)
' Resolve the source range -- works with named ranges or table columns
On Error GoTo Chart_Error
Set srcRng = wsSrc.Range(sourceRangeName)
' Find the chart by name
Set co = wsDash.ChartObjects(chartName)
co.Chart.SetSourceData Source:=srcRng, PlotBy:=xlColumns
Exit Sub
Chart_Error:
MsgBox "Chart update failed for '" & chartName & "': " & _
Err.Description, vbExclamation, "Chart Error"
End Sub
Tip:
cht.SetSourceData Source:=pt.TableRange1connects a chart directly to the PivotTable's range. When the PivotTable refreshes and its layout changes, the chart picks up the new data automatically — you don't need to callSetSourceDataagain after each refresh. This is why PivotCharts are worth using even when you want precise formatting control.
Now we wire everything together. The master RefreshDashboard procedure is what your "Refresh" button will call. It orchestrates the sequence: refresh data → update PivotTables → recalculate KPIs → update charts → give the user feedback.
Add a module called mod_Dashboard.
Option Explicit
' -------------------------------------------------------
' RefreshDashboard
' Master orchestration routine. Assign this to the
' Refresh button on DASHBOARD.
' -------------------------------------------------------
Public Sub RefreshDashboard()
Dim startTime As Double
Dim wsDash As Worksheet
startTime = Timer
Set wsDash = ThisWorkbook.Sheets("DASHBOARD")
' --- Performance: suppress screen flicker and recalc during run ---
Application.ScreenUpdating = False
Application.EnableEvents = False
Application.Calculation = xlCalculationManual
On Error GoTo Refresh_Error
' Step 1: Notify user (write to a status cell on DASHBOARD)
wsDash.Range("kpi_Status").Value = "Refreshing..."
Application.ScreenUpdating = True
DoEvents ' Force the status text to render
Application.ScreenUpdating = False
' Step 2: Refresh all PivotTable caches
' This re-reads from tbl_Sales, picking up any new rows.
Call RefreshAllPivots
' Step 3: Recalculate KPI tiles
Call UpdateKPIs
' Step 4: Chart update (for standalone charts; PivotCharts self-update)
' If you have standalone charts, call UpdateStandaloneChart here.
' Step 5: Calculate elapsed time and update status
Dim elapsed As Double
elapsed = Timer - startTime
wsDash.Range("kpi_Status").Value = _
"Last refreshed: " & Format(Now(), "dd-mmm-yyyy hh:mm:ss") & _
" (" & Format(elapsed, "0.0") & "s)"
GoTo Refresh_Cleanup
Refresh_Error:
wsDash.Range("kpi_Status").Value = "Refresh failed — see error log"
MsgBox "Dashboard refresh error at step: " & Err.Description, _
vbCritical, "Refresh Failed"
Refresh_Cleanup:
Application.Calculation = xlCalculationAutomatic
Application.EnableEvents = True
Application.ScreenUpdating = True
End Sub
' -------------------------------------------------------
' RefreshAllPivots
' Refreshes every PivotTable cache in the workbook once,
' avoiding redundant refreshes when multiple PivotTables
' share the same cache.
' -------------------------------------------------------
Private Sub RefreshAllPivots()
Dim pc As PivotCache
Dim refreshed As Object
Set refreshed = CreateObject("Scripting.Dictionary")
For Each pc In ThisWorkbook.PivotCaches
' PivotCaches have a numeric index; use it to deduplicate
If Not refreshed.exists(pc.Index) Then
If pc.IsConnected Or pc.SourceType = xlDatabase Then
On Error Resume Next
pc.Refresh
On Error GoTo 0
refreshed(pc.Index) = True
End If
End If
Next pc
End Sub
' -------------------------------------------------------
' ResetAllFilters
' Clears all slicer selections. Assign to a Reset button.
' -------------------------------------------------------
Public Sub ResetAllFilters()
Call SelectAllSlicerItems("Slicer_Region")
Call SelectAllSlicerItems("Slicer_Product1")
Call UpdateKPIs
End Sub
The GoTo Refresh_Cleanup pattern in RefreshDashboard is deliberate. Whether the routine succeeds or fails, we must restore Application.Calculation, Application.EnableEvents, and Application.ScreenUpdating to their normal states. Leaving Calculation = xlCalculationManual accidentally is one of the most disorienting bugs a user can experience — formulas stop updating and nobody knows why.
The manual Refresh button is fine, but sophisticated dashboards update KPIs automatically when a slicer selection changes. This requires a worksheet event.
In the VBA Editor, double-click the DASHBOARD sheet object (not a module — the actual sheet). This opens the sheet's code module. Add this:
Option Explicit
' -------------------------------------------------------
' Worksheet_PivotTableUpdate
' Fires whenever any PivotTable connected to this sheet
' is updated — including when a slicer filter changes.
' -------------------------------------------------------
Private Sub Worksheet_PivotTableUpdate(ByVal Target As PivotTable)
' Guard against infinite loops: if we're already updating, exit
Static isUpdating As Boolean
If isUpdating Then Exit Sub
isUpdating = True
On Error GoTo Event_Error
' Recalculate KPIs to reflect new slicer state
Call UpdateKPIs
GoTo Event_Cleanup
Event_Error:
' Silent error handling in events — avoid modal dialogs mid-interaction
Debug.Print "PivotTableUpdate error: " & Err.Description
Event_Cleanup:
isUpdating = False
End Sub
Worksheet_PivotTableUpdate fires when any PivotTable on that sheet updates — but our PivotTables are on the hidden PIVOT sheet, not DASHBOARD. There's a nuance here: slicers on DASHBOARD that are connected to PivotTables on PIVOT will trigger the PIVOT sheet's Worksheet_PivotTableUpdate event, not DASHBOARD's.
So open the PIVOT sheet's code module and add the same event handler there:
Option Explicit
Private Sub Worksheet_PivotTableUpdate(ByVal Target As PivotTable)
Static isUpdating As Boolean
If isUpdating Then Exit Sub
isUpdating = True
On Error Resume Next
Call UpdateKPIs
isUpdating = False
End Sub
Now every slicer interaction triggers a KPI recalculation within milliseconds. The dashboard feels genuinely live.
Warning:
Worksheet_PivotTableUpdatecan fire multiple times during a single user action if several PivotTables share a slicer cache. TheStatic isUpdatingflag preventsUpdateKPIsfrom running in a recursive loop. Always include this guard in event-driven procedures that call routines which might themselves trigger events.
Adding the buttons is straightforward:
DASHBOARD, go to Developer > Insert > Button (Form Control). Draw a button in a clean area of the sheet.RefreshDashboard.ResetAllFilters, label it "✕ Reset Filters."Form Controls are preferable to ActiveX Controls for buttons in production dashboards. They're simpler, more stable across Excel versions, and don't require trust center adjustments on most corporate machines.
Static cell formatting doesn't respond to the data. Let's make the KPI tiles change color dynamically based on performance thresholds — green when Revenue vs. Target is above 95%, amber between 80-95%, red below 80%.
Add this to mod_KPI:
' -------------------------------------------------------
' FormatKPITile
' Applies traffic-light background color to a named range
' based on a numeric value and threshold boundaries.
' -------------------------------------------------------
Public Sub FormatKPITile(ByVal tileName As String, _
ByVal value As Double, _
ByVal greenThreshold As Double, _
ByVal amberThreshold As Double)
Dim wsDash As Worksheet
Dim tile As Range
Set wsDash = ThisWorkbook.Sheets(DASH_SHEET)
Set tile = wsDash.Range(tileName)
' Apply background and font color
With tile.Interior
If value >= greenThreshold Then
.Color = RGB(198, 239, 206) ' Light green
ElseIf value >= amberThreshold Then
.Color = RGB(255, 235, 156) ' Light amber
Else
.Color = RGB(255, 199, 206) ' Light red
End If
End With
' Matching dark text for readability
With tile.Font
If value >= greenThreshold Then
.Color = RGB(0, 97, 0)
ElseIf value >= amberThreshold Then
.Color = RGB(156, 87, 0)
Else
.Color = RGB(156, 0, 6)
End If
End With
End Sub
Then call this inside UpdateKPIs, right after writing the kpi_VsTarget value:
' Apply traffic-light formatting to the vs-target KPI
Call FormatKPITile("kpi_VsTarget", vsTargetPct / 100, 0.95, 0.80)
Now the tile turns green, amber, or red automatically based on filtered data — no Conditional Formatting rules that could get corrupted, no dependencies on Excel's recalculation engine.
Build the complete dashboard described in this lesson from scratch. Here's your structured challenge:
Part 1 — Data Layer
Create a tbl_Sales table with at least 200 rows of realistic sales data across 4 regions (North, South, East, West), 5 products, and 12 months. You can generate this with a formula: in a helper column, use =RANDBETWEEN(5000, 50000) for Revenue, =RANDBETWEEN(4500, 55000) for Target.
Part 2 — Calculation Layer
Create pvt_SalesSummary on the PIVOT sheet. Add Region to rows, Date (grouped by month) to columns, and Revenue to Values. Rename the Value Field to "Revenue" (not "Sum of Revenue"). Create slicers for Region and Product and connect them to the PivotTable.
Part 3 — Code
Implement all four modules from this lesson (mod_KPI, mod_Slicers, mod_Charts, mod_Dashboard) in your workbook. Define the named ranges kpi_TotalRevenue, kpi_VsTarget, kpi_TopRegion, and kpi_Status on the DASHBOARD sheet.
Part 4 — Presentation
Add the Refresh and Reset buttons. Run SetupDashboardChart once from the Immediate Window (Call SetupDashboardChart) to create the initial chart. Then test:
tbl_Sales and click Refresh → revenue totals should increaseStretch Goal
Add a third slicer for Rep and wire it to both PivotTables. Write a mod_RepSummary module that reads from pvt_RepPerformance and populates a small ranked list (top 5 reps by revenue) into a range on DASHBOARD.
"Subscript out of range" on PivotTable name
This means PivotTables("pvt_SalesSummary") can't find a PivotTable with that exact name. Open the PIVOT sheet, click inside the PivotTable, go to PivotTable Analyze > PivotTable Name and verify the name matches exactly — including capitalization.
KPIs update but the chart doesn't
PivotCharts self-update when their PivotTable refreshes, but only if the chart is actually connected to the PivotTable. If SetSourceData was pointed at a static range instead of pt.TableRange1, it won't follow the pivot. Re-run SetupDashboardChart to reset the connection.
Slicer items appear greyed out in the object
Greyed-out items in the slicer UI are SlicerItem.HasData = False — they exist in the cache but have no data under the current filter combination. When iterating SlicerItems to calculate Top Region, check pi.HasData in addition to pi.Visible to avoid trying to retrieve a data value for an empty item.
GetPivotData returns an error when a filter leaves no data
pt.GetPivotData("Revenue") throws error 1004 if no data satisfies the current filter. Wrap it in On Error Resume Next and check Err.Number immediately after:
On Error Resume Next
totalRevenue = pt.GetPivotData("Revenue")
If Err.Number <> 0 Then totalRevenue = 0
On Error GoTo KPI_Error
Dashboard refreshes feel slow (>3 seconds)
Check two things: (1) Are you refreshing the same PivotCache multiple times? Use the deduplicated RefreshAllPivots approach shown above. (2) Is Application.Calculation returning to xlCalculationAutomatic at the end? If a previous failed run left it in Manual mode, every subsequent run will appear slow because formulas elsewhere in the workbook aren't calculating.
Worksheet_PivotTableUpdate fires endlessly
Classic sign that the Static isUpdating guard is missing or that UpdateKPIs itself is triggering a PivotTable update (possible if you're writing to a cell range that feeds into the pivot). Verify that your KPI named ranges are entirely outside the PivotTable's footprint.
You've now built a dashboard architecture that separates concerns cleanly, automates every dynamic element through VBA, and responds to user interactions without manual intervention. The patterns here — event-driven KPI updates, programmatic slicer control, named range abstraction, the guard-flag technique for event recursion — are directly transferable to production workbooks at any scale.
Here's what you built:
mod_KPI for data-driven, PivotTable-sourced KPI calculations with traffic-light formattingmod_Slicers for programmatic slicer manipulation, including safe multi-item selectionmod_Charts for chart setup and dynamic range managementmod_Dashboard as the master orchestration layer with proper error handling and Application state managementWorksheet_PivotTableUpdateWhere to go from here:
Power Query integration: Replace the manual tbl_Sales data entry with a Power Query connection. Your RefreshAllPivots routine can be preceded by ThisWorkbook.Connections.Item("Query - SalesData").Refresh to pull fresh data from a database or file before the pivots recalculate.
Dynamic chart selection: Add a dropdown on DASHBOARD that lets users switch the chart between "Revenue by Region," "Revenue by Product," and "Rep Performance." Use a Change event on the dropdown cell to call a routing function that adjusts the chart's SetSourceData and title dynamically.
Export automation: Add a ExportDashboard procedure that screenshots the DASHBOARD sheet as an image (using ws.ExportAsFixedFormat or the CopyPicture method) and either saves it to a folder or attaches it to an Outlook email — making the weekly reporting cycle a single button click.
Error logging: Replace the MsgBox error handlers with a proper logging routine that writes timestamp, procedure name, and error description to a hidden LOG sheet. In a shared workbook environment, this is invaluable for diagnosing intermittent failures reported by non-technical users.
The dashboard you built today is a foundation. In a real engagement, you'd add user access controls, a configuration sheet for thresholds and slicer cache names (instead of hardcoded constants), and probably a Workbook_Open event that runs RefreshDashboard automatically so the data is always fresh when someone opens the file. Each of those is a short step from where you are now.
Learning Path: Advanced Excel & VBA