Page 1 of 2 12 LastLast
Results 1 to 10 of 13

Thread: Automate the creation of graph from Pivot Table - VBA
Thanks Thanks: 0 Likes Likes: 0

  1. #1
    New Member
    Join Date
    May 2019
    Posts
    32
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Automate the creation of graph from Pivot Table - VBA

    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

  2. #2
    New Member
    Join Date
    May 2019
    Posts
    32
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Automate the creation of graph from Pivot Table - VBA

    Hi All, any suggestions? THanks

  3. #3
    MrExcel MVP
    Join Date
    Mar 2004
    Location
    Canada
    Posts
    18,722
    Post Thanks / Like
    Mentioned
    29 Post(s)
    Tagged
    5 Thread(s)

    Default Re: Automate the creation of graph from Pivot Table - VBA

    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!

  4. #4
    New Member
    Join Date
    May 2019
    Posts
    32
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Automate the creation of graph from Pivot Table - VBA

    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

  5. #5
    MrExcel MVP
    Join Date
    Mar 2004
    Location
    Canada
    Posts
    18,722
    Post Thanks / Like
    Mentioned
    29 Post(s)
    Tagged
    5 Thread(s)

    Default Re: Automate the creation of graph from Pivot Table - VBA

    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?

  6. #6
    New Member
    Join Date
    May 2019
    Posts
    32
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Automate the creation of graph from Pivot Table - VBA

    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

  7. #7
    MrExcel MVP
    Join Date
    Mar 2004
    Location
    Canada
    Posts
    18,722
    Post Thanks / Like
    Mentioned
    29 Post(s)
    Tagged
    5 Thread(s)

    Default Re: Automate the creation of graph from Pivot Table - VBA

    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?

  8. #8
    New Member
    Join Date
    May 2019
    Posts
    32
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Automate the creation of graph from Pivot Table - VBA

    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

  9. #9
    MrExcel MVP
    Join Date
    Mar 2004
    Location
    Canada
    Posts
    18,722
    Post Thanks / Like
    Mentioned
    29 Post(s)
    Tagged
    5 Thread(s)

    Default Re: Automate the creation of graph from Pivot Table - VBA

    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?

  10. #10
    New Member
    Join Date
    May 2019
    Posts
    32
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Automate the creation of graph from Pivot Table - VBA

    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.

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •