none
duplicate xlBarClustered chart on new data set RRS feed

  • Question

  • Hi,

    I download the Excel data set from https://go.microsoft.com/fwlink/?LinkID=521962.

    With the following code below. I was able to produce two pivot tables and two charts.

    But on my 2nd xlBarClustered chart is duplicate value from the first one. How do I control my chart #2 is to show data from pivot table 2?

    Thanks.


    Worksheets("Sheet1").Select
        Dim pivotCache As pivotCache
        Dim pivotTable As pivotTable
        
        Set pivotCache = ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, _
            SourceData:="Sheet1" + "!" & Worksheets("Sheet1").Range("A1").CurrentRegion.Address)
        
        Set pivotTable = pivotCache.CreatePivotTable( _
            TableDestination:="Sheet2" + "!R2C2", TableName:="PivotTable1")
    
        With pivotTable
            .AddFields RowFields:="Segment"
            .AddDataField .PivotFields("Country"), "Count of Country", xlCount
            .AddDataField .PivotFields("Sale Price"), "Sum of Sale Price", xlSum
            .AddFields RowFields:=Array("Segment")
        End With
        pivotTable.CompactLayoutRowHeader = "Segment"
        
        
        Set pivotTable = pivotCache.CreatePivotTable( _
            TableDestination:="Sheet2" + "!R2C7", TableName:="PivotTable2")
    
        With pivotTable
            .AddFields RowFields:="Product"
            .AddDataField .PivotFields(" Sales"), "Sum of Sales", xlSum
            .AddFields RowFields:=Array("Product")
        End With
        pivotTable.CompactLayoutRowHeader = "Product"
         
        
        
        
        
        
        Worksheets("Sheet2").Select
        ' chart 1
        ActiveSheet.Shapes.AddChart2(216, xlBarClustered).Select
        ActiveChart.SetSourceData Source:=Range("Sheet2!$B$3:$D$9")
        ActiveChart.SetElement (msoElementLegendNone)
        ActiveChart.SetElement (msoElementDataLabelOutSideEnd)
        ActiveChart.SetElement (msoElementPrimaryValueGridLinesNone)
    
        'chart 2
        ActiveSheet.Shapes.AddChart2(216, xlBarClustered).Select
        ActiveChart.SetSourceData Source:=Range("Sheet2!$G$3:$H$9")
        ActiveChart.SetElement (msoElementLegendNone)
        ActiveChart.SetElement (msoElementDataLabelOutSideEnd)
        ActiveChart.SetElement (msoElementPrimaryValueGridLinesNone)


    • Edited by slick.user Monday, March 23, 2020 6:24 AM typo
    Monday, March 23, 2020 6:24 AM

Answers

  • This error is mentioned very frequently. None of the suggestions that I found work consistently.

    I'd delete Sheet2 and create it again, by adding this code at the beginning of the macro:

        Application.DisplayAlerts = False
        Worksheets("Sheet2").Delete
        Application.DisplayAlerts = True
        Worksheets.Add.Name = "Sheet2"


    Regards, Hans Vogelaar (http://www.eileenslounge.com)

    • Marked as answer by slick.user Monday, March 23, 2020 11:26 PM
    Monday, March 23, 2020 5:04 PM

All replies

  • I ran your code and got this:

    Originally, the charts were on top of each other, I moved them apart. Could that be your problem?


    Regards, Hans Vogelaar (http://www.eileenslounge.com)

    Monday, March 23, 2020 8:34 AM
  • Hans Vogelaar,

    I'm using Office 365.

    I deleted all content in Sheet2 and re-run the code. I was able to get two different charts stack on top of each other. I moved it apart, delete the content in Sheet2, re-run the code again and got the error below.

    Thanks.

    Monday, March 23, 2020 4:39 PM
  • This error is mentioned very frequently. None of the suggestions that I found work consistently.

    I'd delete Sheet2 and create it again, by adding this code at the beginning of the macro:

        Application.DisplayAlerts = False
        Worksheets("Sheet2").Delete
        Application.DisplayAlerts = True
        Worksheets.Add.Name = "Sheet2"


    Regards, Hans Vogelaar (http://www.eileenslounge.com)

    • Marked as answer by slick.user Monday, March 23, 2020 11:26 PM
    Monday, March 23, 2020 5:04 PM
  • Thanks.
    Monday, March 23, 2020 11:26 PM