Automate the creation of graph from Pivot Table - VBA

B

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.


[TABLE="width: 681"]
<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>[TR]
[TD="width: 305"]Count of Status[/TD]
[TD="width: 129"]Column Labels[/TD]
[TD="width: 27"] [/TD]
[TD="width: 96"] [/TD]
[TD="width: 93"] [/TD]
[TD="width: 101"] [/TD]
[TD="width: 91"] [/TD]
[TD="width: 65, bgcolor: transparent"] [/TD]
[/TR]
[TR]
[TD]Row Labels[/TD]
[TD]> 5 Days[/TD]
[TD]NA[/TD]
[TD]Uncorrected[/TD]
[TD]Within 1 Day[/TD]
[TD]Within 5 Days[/TD]
[TD]Grand Total[/TD]
[TD="bgcolor: transparent"] [/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]Claims - Credit & Surety Claims[/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"]1[/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"]1[/TD]
[TD="bgcolor: transparent"] [/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]Claims - Long Tail[/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"]2[/TD]
[TD="bgcolor: transparent"]2[/TD]
[TD="bgcolor: transparent"] [/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]Claims - Short Tail[/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"]6[/TD]
[TD="bgcolor: transparent"]1[/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"]7[/TD]
[TD="bgcolor: transparent"] [/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]Claims - Workers Compensation - Claims[/TD]
[TD="bgcolor: transparent"]1[/TD]
[TD="bgcolor: transparent"]1[/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"]3[/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"]5[/TD]
[TD="bgcolor: transparent"] [/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]Policy Support Services - Credit Control[/TD]
[TD="bgcolor: transparent"]1[/TD]
[TD="bgcolor: transparent"]4[/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"]6[/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"]11[/TD]
[TD="bgcolor: transparent"] [/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]Policy Support Services - Elders[/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"]2[/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"]4[/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"]6[/TD]
[TD="bgcolor: transparent"] [/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]Policy Support Services - Financial Institution[/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"]2[/TD]
[TD="bgcolor: transparent"]3[/TD]
[TD="bgcolor: transparent"]5[/TD]
[TD="bgcolor: transparent"]1[/TD]
[TD="bgcolor: transparent"]11[/TD]
[TD="bgcolor: transparent"] [/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]Policy Support Services - Motorcycle[/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"]4[/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"]4[/TD]
[TD="bgcolor: transparent"] [/TD]
[/TR]
[TR]
[TD]Grand Total[/TD]
[TD]2[/TD]
[TD]19[/TD]
[TD]4[/TD]
[TD]19[/TD]
[TD]3[/TD]
[TD]47[/TD]
[TD="bgcolor: transparent"] [/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"] [/TD]
[/TR]
</tbody>[/TABLE]
 

Excel Facts

Copy formula down without changing references
If you have =SUM(F2:F49) in F50; type Alt+' in F51 to copy =SUM(F2:F49) to F51, leaving the formula in edit mode. Change SUM to COUNT.
Sort by date Sort by votes
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!
 
Upvote 0
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
 
Upvote 0
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?
 
Upvote 0
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
 
Upvote 0
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?
 
Upvote 0
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
 
Upvote 0
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?
 
Upvote 0
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.
 
Upvote 0
You must log in or register to reply here.

Similar threads

F
  • Question Question
Pasting a pivot table into values, allowing me to edit the numbers, while maintaing the subtotal/grandtotal sum formula
Replies
0
Views
518
fanthor
F
S
  • Question Question
Help with loops to create pivot tables and arrange graphs in different orders
Replies
0
Views
781
serrulate
S
J
  • Question Question
Lookup from Pivot Table based on Multiple Criteria
Replies
1
Views
217
shift-del
shift-del
E
  • Question Question
Can anyone help me get my VBA code working? I have spent tons of time but can't quite fix it.
Replies
3
Views
3K
profray
P
C
  • Solved
Is there a way of automating pivot tables using VBA?
Replies
2
Views
587
Coyotex3
C

Forum statistics

Threads
1,223,904
Messages
6,175,295
Members
452,631
Latest member
a_potato

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top