How to create a PivotChart from a PivotTable using VBA?

Creating a PivotChart from a PivotTable using VBA involves a few steps where you set up the PivotChart based on an existing PivotTable. Here is a step-by-step guide to do it:

Sub CreatePivotChartFromPivotTable()
    Dim ws As Worksheet
    Dim pvt As PivotTable
    Dim pvtChart As ChartObject
    
    ' Reference the worksheet containing the PivotTable
    Set ws = ThisWorkbook.Sheets("Sheet1") ' Change "Sheet1" to your sheet name

    ' Reference the PivotTable
    Set pvt = ws.PivotTables("PivotTable1") ' Change "PivotTable1" to your PivotTable name

    ' Add a new ChartObject to the worksheet
    Set pvtChart = ws.ChartObjects.Add(Left:=300, Top:=5, Width:=400, Height:=200) ' Adjust positioning as needed
    
    ' Set the source of the chart to the PivotTable
    With pvtChart
        .Chart.SetSourceData Source:=pvt.TableRange1
        .Chart.ChartType = xlColumnClustered ' Change to desired chart type
    End With
    
    ' Optionally, set chart title and other properties
    With pvtChart.Chart
        .HasTitle = True
        .ChartTitle.Text = "PivotChart Title" ' Set the title as needed
        ' You can modify further chart properties here
    End With

End Sub

Prepare Your Data: Make sure you have your data set and a PivotTable created from it. The PivotTable must already exist in your Excel workbook.
Open VBA Editor:

Press `ALT + F11` to open the VBA editor.

Insert a Module:

Go to `Insert` > `Module` to add a new module where you will write your VBA code.

Write the VBA Code:

You can use the following VBA code as a template to create a PivotChart from an existing PivotTable. This example assumes your PivotTable is on the first worksheet and is named “PivotTable1”.

Run the Macro:

Press `F5` or run the macro to execute it. This will create a PivotChart on the specified worksheet.

Key Points:

Update `”Sheet1″` with the actual sheet name containing your PivotTable.
Change `”PivotTable1″` to the actual name of your PivotTable.
Adjust the `Left`, `Top`, `Width`, and `Height` parameters to position the chart as desired on your worksheet.
You can modify the `ChartType` (e.g., `xlBarClustered`, `xlLine`, etc.) and other properties to suit your needs.

This VBA script will create a PivotChart on the same worksheet as your PivotTable. If you want to position it on another sheet or customize it further, adjust the references and properties accordingly.

facebooklinkedin