[Excel VBA] How to Create a Dashboard That Displays Graphs on Button Click

2025年12月27日

When summarizing information visually in Excel, it is very convenient to have a “dashboard where charts appear when a button is pressed.”

In this article, I will introduce a method using VBA to copy charts from another sheet and display them at specific positions on a dashboard sheet.

Goal Specifications

We will implement the following structure:

Place a button on the “Dashboard” sheet.
When the button is pressed, copy multiple charts from “Sheet2” to the “Dashboard”.
The paste locations are pre-defined in an array.

VBA Code

Below is the VBA code used. Assign this macro to a button, and the charts will automatically appear when clicked.

Sub CopyGraphsToDashboard()
    Dim sourceSheet As Worksheet
    Dim dashboardSheet As Worksheet
    Dim graphNames As Variant
    Dim cellLocations As Variant
    Dim i As Integer

    ' Chart names and corresponding cell locations on the dashboard
    graphNames = Array("sheet2_graph1", "sheet2_graph2", "sheet2_graph3")
    cellLocations = Array("E7", "E21", "E35")

    Set sourceSheet = ThisWorkbook.Sheets("Sheet2")
    Set dashboardSheet = ThisWorkbook.Sheets("Dashboard")

    For i = LBound(graphNames) To UBound(graphNames)
        ' Copy the chart
        sourceSheet.ChartObjects(graphNames(i)).Copy

        ' Paste at the specified location
        With dashboardSheet
            .Paste .Range(cellLocations(i))
            ' Adjust the position of the pasted chart to align with the cell
            With .ChartObjects(.ChartObjects.Count)
                .Top = .Range(cellLocations(i)).Top
                .Left = .Range(cellLocations(i)).Left
            End With
        End With
    Next i
End Sub

Key Points of the Code

1. Managing Chart Names and Locations with Arrays

graphNames = Array("sheet2_graph1", "sheet2_graph2", "sheet2_graph3")
cellLocations = Array("E7", "E21", "E35")

The target charts and the cell addresses where they should be pasted are defined in arrays. This approach makes the code flexible; even if the number of charts increases, you only need to update the arrays.

2. Retrieving and Placing Charts with ChartObjects

sourceSheet.ChartObjects(graphNames(i)).Copy

The code copies the target chart using its name.

.Paste .Range(cellLocations(i))

It then pastes the chart into the specified cell range. The code also fine-tunes the position (.Top and .Left) to ensure the chart aligns perfectly with the destination cell.

Tips for Customization

Change Content: You can freely change which charts are displayed or where they are placed simply by modifying the contents of the arrays.
Resize: You can also adjust the size or style of the charts using ChartObjects properties after pasting.
Integration: This method can be applied to consolidate different types of charts from multiple sheets into one view.

Summary

By using Excel VBA, you can easily build a dashboard where charts expand upon a button click. The method introduced here uses a combination of arrays and loops, making it highly scalable.