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.


[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]

 

[Domenic]

When you say "generates a raw file", do you mean that the data is imported into your workbook? If so, there's no need to keep re-creating your pivottable and pivotchart. If you are adding new data to the exising one, after you do so simply adjust the range for the source data for your pivottable, and refresh. If you are replacing the existing data with a new set of data, first clear the existing one, then import the new data, then change/update the source data for the pivottable, and then refresh. In the latter case, you may also need to re-add your pivot fields for the report.


At this point, if you simply want my original macro to be amended as you've described, here it is. It will first delete any chart that already exists, and then it creates a new one. I've assumed that "Sheet1" will contain the chart. Also, the newly created chart will be named "Chart 1". Change these accordingly.

Sub CreatePivotChart()


    Dim destWorksheet As Worksheet
    Dim sourcePivotTable As pivotTable
    Dim sourceRange As Range
    Dim rowGrand As Long
    Dim colGrand As Long
    
    Const CHART_WORKSHEET As String = "Sheet1"
    Const CHART_NAME As String = "Chart 1"
    
    On Error Resume Next
    Application.DisplayAlerts = False
    ThisWorkbook.Worksheets(CHART_WORKSHEET).ChartObjects(CHART_NAME).Delete
    Application.DisplayAlerts = True
    On Error GoTo 0
    
    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(CHART_WORKSHEET)
    
    With destWorksheet
        With .ChartObjects.Add(Left:=.Range("B2").Left, Top:=.Range("B2").Top, Width:=-1, Height:=-1)
            .Name = CHART_NAME
            With .Chart
                .ChartType = xlColumnClustered
                .SetSourceData sourceRange
            End With
        End With
    End With




End Sub

 

[Bryan123]

Hi Dom,
Thank you very much for sharing your time. You code works perfectly! THank you!

 

[Domenic]

You're very welcome!

Cheers!