Automate the creation of graph from Pivot Table - VBA

Bryan123

New Member
Joined
May 23, 2019
Messages
41
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 StatusColumn Labels
Row Labels> 5 DaysNAUncorrectedWithin 1 DayWithin 5 DaysGrand Total
Claims - Credit & Surety Claims 1 1
Claims - Long Tail 22
Claims - Short Tail 61 7
Claims - Workers Compensation - Claims11 3 5
Policy Support Services - Credit Control14 6 11
Policy Support Services - Elders 2 4 6
Policy Support Services - Financial Institution 235111
Policy Support Services - Motorcycle 4 4
Grand Total219419347
<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>
 

Some videos you may like

Excel Facts

Excel Can Read to You
Customize Quick Access Toolbar. From All Commands, add Speak Cells or Speak Cells on Enter to QAT. Select cells. Press Speak Cells.

Domenic

MrExcel MVP
Joined
Mar 10, 2004
Messages
19,193
Here's an example, in which you'll need to change the relevant references to suit your needs...

Code:
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

New Member
Joined
May 23, 2019
Messages
41
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

MrExcel MVP
Joined
Mar 10, 2004
Messages
19,193
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

New Member
Joined
May 23, 2019
Messages
41
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

MrExcel MVP
Joined
Mar 10, 2004
Messages
19,193
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...

Code:
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

New Member
Joined
May 23, 2019
Messages
41
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

MrExcel MVP
Joined
Mar 10, 2004
Messages
19,193
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

New Member
Joined
May 23, 2019
Messages
41
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,099,992
Messages
5,471,855
Members
406,789
Latest member
gkfcosta

This Week's Hot Topics

Top