[Excel VBA] How to Duplicate a Chart and Set New Data

2025年12月26日

Introduction

In Excel, you can efficiently create new charts by using VBA to duplicate (copy) an existing one and then assigning a different data range to the copy. This allows you to generate multiple charts while maintaining a consistent design and layout without having to build each one from scratch.

In this article, I will explain how to use VBA to duplicate a chart, offset its position so it sits next to the original, and redraw it with a different data source.

Sample Code: Duplicate and Replace Data

The following macro duplicates the first chart on the active sheet and assigns new data to it.

Sub DuplicateChartAndSetNewData()
    Dim baseChart As ChartObject
    Dim copiedChartShape As Shape

    ' 1. Get the source chart (the first chart on the sheet)
    Set baseChart = ActiveSheet.ChartObjects(1)

    ' 2. Duplicate the chart (The return value is a Shape object)
    Set copiedChartShape = baseChart.Duplicate

    ' 3. Change the position and data range of the duplicated chart
    With copiedChartShape
        .Top = baseChart.Top
        .Left = baseChart.Left + baseChart.Width + 30 ' Place 30 points to the right
        
        ' Set the new data source (e.g., Labels in B2:B5, Values in D2:D5)
        .Chart.SetSourceData Source:=Range("B2:B5,D2:D5")
    End With

    ' 4. Copy formatting from the original (Optional)
    ' Sometimes duplication resets specific formats, so we paste formats again to be safe.
    baseChart.Copy
    copiedChartShape.Select
    ActiveSheet.PasteSpecial Format:=2 ' 2 = xlPasteFormats
End Sub

Code Explanation

1. Identify the Target Chart

Set baseChart = ActiveSheet.ChartObjects(1) This targets the first chart inserted on the active sheet. If you have a specific chart name, you can use ChartObjects("Chart 1") for better precision.

2. Duplicate the Chart

Set copiedChartShape = baseChart.Duplicate Using the Duplicate method copies the appearance and structure. Note that this method returns a Shape object, so we access the chart properties via .Chart in the next step.

3. Adjust the Position

.Top = baseChart.Top
.Left = baseChart.Left + baseChart.Width + 30

This logic places the new chart immediately to the right of the original chart with a 30-point gap.

4. Set the New Data Range

.Chart.SetSourceData Source:=Range("B2:B5,D2:D5") This is the most critical step. It assigns a new data range to the duplicated chart. By changing the range specified here (e.g., using a variable for rows), you can dynamically switch what the chart displays.

5. Copy and Paste Formatting

baseChart.Copy
copiedChartShape.Select
ActiveSheet.PasteSpecial Format:=2

While Duplicate copies most settings, complex formatting sometimes resets. Explicitly copying the original and using PasteSpecial with Format:=2 (xlPasteFormats) ensures the new chart looks exactly like the original.

Common Use Cases

Mass Production: Creating 10 charts for 10 different products using the same design.
Comparison: Placing charts side-by-side to compare different time periods.
Templates: Creating a “master chart” and dynamically generating reports based on it.

Summary

By using the Duplicate method, you can easily clone existing charts. Combined with SetSourceData, you can rapidly produce multiple charts with different data but identical styling.

Visual adjustments on charts are often time-consuming when done manually, so automating this with VBA leads to significant time savings.

よかったらシェアしてね！