Building a Dynamic Excel Dashboard with VBA: Automate Charts, Slicers, and KPI Summaries That Update on Demand

Introduction

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:

• How to structure a VBA-powered dashboard so the code, data, and presentation layers stay cleanly separated
• How to automate slicer selections programmatically using SlicerCache objects
• How to write KPI calculation routines that populate summary tiles dynamically using GETPIVOTDATA-style logic in VBA
• How to build chart update routines that handle changing data ranges gracefully
• How to wire everything together with a master Refresh procedure triggered by a button or workbook event

---

Prerequisites

You should be comfortable with:

• PivotTables and PivotCharts (creating, filtering, understanding the cache)
• Basic VBA: the Editor, Sub/Function syntax, working with Ranges and Worksheets
• Excel data modeling concepts — named tables (ListObject), structured references

You do not need to be a VBA expert. We'll explain every non-obvious piece of code as we go.

---

Setting Up the Workbook Architecture

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:

1. Create three sheets named DATA, PIVOT, and DASHBOARD.
2. On DATA, paste or import your sales records and format them as a Table (Insert > Table) named tbl_Sales.
3. On 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.
4. On 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.
5. Right-click the 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.

---

Writing the KPI Calculation Engine

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.

---

Controlling Slicers Programmatically

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).

---

Building the Chart Update Routine

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

---

The Master Refresh Procedure

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.

---

Responding to Slicer Changes with Worksheet Events

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.

---

Wiring Up the Buttons

Adding the buttons is straightforward:

1. On DASHBOARD, go to Developer > Insert > Button (Form Control). Draw a button in a clean area of the sheet.
2. When the "Assign Macro" dialog appears, select RefreshDashboard.
3. Right-click the button and choose "Edit Text." Type "⟳ Refresh Dashboard."
4. Add a second button the same way, assign 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.

---

Adding Conditional KPI Formatting with VBA

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.

---

Hands-On Exercise

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:

• Click Refresh → KPI tiles should populate
• Select a single region in the slicer → Top Region KPI should update automatically
• Click Reset Filters → all KPIs return to showing all data
• Add 5 new rows to tbl_Sales and click Refresh → revenue totals should increase

Stretch 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.

---

Common Mistakes & Troubleshooting

"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.

---

Summary & Next Steps

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:

• A three-layer workbook architecture (data / calculation / presentation)
• mod_KPI for data-driven, PivotTable-sourced KPI calculations with traffic-light formatting
• mod_Slicers for programmatic slicer manipulation, including safe multi-item selection
• mod_Charts for chart setup and dynamic range management
• mod_Dashboard as the master orchestration layer with proper error handling and Application state management
• Event-driven auto-update via Worksheet_PivotTableUpdate

Where 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.