Automate the creation of graph from Pivot Table - VBA

[Bryan123]

Hi All,

I have created a VBA that automatically creates a pivot table. However, I would also like to automate the creation of a graph from the the pivot table. What I want to happen is to make a graph from the details without including "Row Labels" and "Grand Total". If there is a new entry for row, how do I capture it as well? Please help me.

Count of Status	Column Labels
Row Labels	> 5 Days	NA	Uncorrected	Within 1 Day	Within 5 Days	Grand Total
Claims - Credit & Surety Claims				1		1
Claims - Long Tail					2	2
Claims - Short Tail		6	1			7
Claims - Workers Compensation - Claims	1	1		3		5
Policy Support Services - Credit Control	1	4		6		11
Policy Support Services - Elders		2		4		6
Policy Support Services - Financial Institution		2	3	5	1	11
Policy Support Services - Motorcycle		4				4
Grand Total	2	19	4	19	3	47

<colgroup><col width="305" style="width: 229pt; mso-width-source: userset; mso-width-alt: 9760;"> <col width="129" style="width: 97pt; mso-width-source: userset; mso-width-alt: 4128;"> <col width="27" style="width: 20pt; mso-width-source: userset; mso-width-alt: 864;"> <col width="96" style="width: 72pt; mso-width-source: userset; mso-width-alt: 3072;"> <col width="93" style="width: 70pt; mso-width-source: userset; mso-width-alt: 2976;"> <col width="101" style="width: 76pt; mso-width-source: userset; mso-width-alt: 3232;"> <col width="91" style="width: 68pt; mso-width-source: userset; mso-width-alt: 2912;"> <col width="65" style="width: 49pt; mso-width-source: userset; mso-width-alt: 2080;"> <tbody> </tbody>

 

[Bryan123]

Hi All, any suggestions? THanks

 

[Domenic]

Here's an example, in which you'll need to change the relevant references to suit your needs...

Option Explicit

Sub CreatePivotChart()


    Dim destWorksheet As Worksheet
    Dim sourcePivotTable As pivotTable
    Dim sourceRange As Range
    Dim rowGrand As Long
    Dim colGrand As Long
    
    Set sourcePivotTable = ThisWorkbook.Worksheets("Sheet1").PivotTables("PivotTable1")
    
    With sourcePivotTable
        rowGrand = IIf(.rowGrand, 1, 0)
        colGrand = IIf(.ColumnGrand, 1, 0)
        With .TableRange1
            Set sourceRange = .Resize(.Rows.Count - rowGrand, .Columns.Count - colGrand)
        End With
    End With
    
    Set destWorksheet = ThisWorkbook.Worksheets.Add
    
    With destWorksheet
        With .Shapes.AddChart2(Style:=201, XlChartType:=xlColumnClustered, Left:=.Range("B2").Left, Top:=.Range("B2").Top)
            .Chart.SetSourceData sourceRange
        End With
    End With
    
End Sub

Hope this helps!

 

[Bryan123]

Hi, I get an error Run-time error 438:
Object doesnt support this property or method
My needed data(row) starts from A5 by the way. Thanks

 

[Domenic]

Did you make any changes to the macro? If so, can you post the exact code that you're using? Also, can you specify which line is giving you that error?

 

[Bryan123]

Hi, please see changes made below. I get the error from this line:
With destWorksheet
With .Shapes.AddChart2(Style:=201, XlChartType:=xlColumnClustered, Left:=.Range("B2").Left, Top:=.Range("B2").Top)
.Chart.SetSourceData sourceRange


---------------
Option Explicit
Sub CreatePivotChart()

Dim destWorksheet As Worksheet
Dim sourcePivotTable As PivotTable
Dim sourceRange As Range
Dim rowGrand As Long
Dim colGrand As Long

Set sourcePivotTable = ThisWorkbook.Worksheets("Pivot").PivotTables("PivotTable1")

With sourcePivotTable
rowGrand = IIf(.rowGrand, 1, 0)
colGrand = IIf(.ColumnGrand, 1, 0)
With .TableRange1
Set sourceRange = .Resize(.Rows.Count - rowGrand, .Columns.Count - colGrand)
End With
End With

Set destWorksheet = ThisWorkbook.Worksheets.Add

With destWorksheet
With .Shapes.AddChart2(Style:=201, XlChartType:=xlColumnClustered, Left:=.Range("B2").Left, Top:=.Range("B2").Top)
.Chart.SetSourceData sourceRange
End With
End With

End Sub

 

[Domenic]

The error is probably due the version of Excel you're using. If I'm not mistaken, Shapes.AddChart2 method is only available in Excel 2013. Therefore, here's an alternative method...

Sub CreatePivotChart()

    Dim destWorksheet As Worksheet
    Dim sourcePivotTable As pivotTable
    Dim sourceRange As Range
    Dim rowGrand As Long
    Dim colGrand As Long
    
    Set sourcePivotTable = ThisWorkbook.Worksheets("Pivot").PivotTables("PivotTable1")
    
    With sourcePivotTable
        rowGrand = IIf(.rowGrand, 1, 0)
        colGrand = IIf(.ColumnGrand, 1, 0)
        With .TableRange1
            Set sourceRange = .Resize(.Rows.Count - rowGrand, .Columns.Count - colGrand)
        End With
    End With
    
    Set destWorksheet = ThisWorkbook.Worksheets.Add
    
    With destWorksheet
        With .ChartObjects.Add(Left:=.Range("B2").Left, Top:=.Range("B2").Top, Width:=-1, Height:=-1)
            With .Chart
                .ChartType = xlColumnClustered
                .SetSourceData sourceRange
            End With
        End With
    End With


End Sub

Note that the width and height are both set at -1, which will set the properties to their default size. Change these values as desired.

Does this help?

 

[Bryan123]

Hey, that's amazing. It works like a miracle but I have one little request if I may. I have created a sheet "Graph" and instead of creating new sheets everytime the program runs , I'd like to have the graph updated always on this sheet. Is it possible? Thank You

 

[Domenic]

Just in case you may not already be aware of it, the chart will automatically update as the pivot table is updated. And, you can set it up so that the range for the source data gets automatically adjusted when data is added or removed. So, for example, first you would convert your data into a Table (Ribbon >> Insert >> Table). Then, when data is added or removed, the range automatically adjusts. Then, you simply need to refresh the pivot table, and the chart will automatically update.

Do you still want me to amend the macro as you've described?

 

[Bryan123]

Hi Dom,

Thanks for the quick revert. My actual project has a macro that immediately runs when the file is opened. It generates a raw file, then creates a pivot table out of the raw and creates a graph from the pivot. My pivot table clears before generating a new one.
If I include your code, the file keeps creating a new sheet for the graph whenever I open the file. Would it be ok if you share a code on which it clears the graph and create a new one only on the "Graph" sheet? Thank you very much for the help.