locked
Excel chart picking up data from a default source RRS feed

  • Question

  • I am wanting to produce an excel workbook with various reports.

    On Sheet #1 I have two pivot tables (both using different columns from same source query). On Sheet#2 I have another pivot table. I want to produce a chart on Sheet#1 using the pivot data on Sheet #2. I keep getting "set source" errors using various combinations.(Range, PivotTable Range, etc.). If I allow to procede following the error, a chart is produced using the data from the first pivot on Sheet #1, i.e. it seems to default to using the data in the 1st pivit without even asking.

    Iy just won't let me point the source for the chart towards trhe pivot (or underlying data) on Sheet #2.

    This is on Access 2010.

    Any help would be gratefully received.

    Monday, April 10, 2017 6:53 PM

All replies

  • Instead of a pivot table, insert a pivot chart - very similar to create and work with, with some limitations on the types of charts allowed. 
    Monday, April 10, 2017 7:24 PM
  • Amongst the various combinations I have tried was :

    With Sheet
    .Shapes.AddChart ....etc.

    Same problem. With any variation the issue is with .Set SourceData Source:=  .....

    I have referenced the data I want in  many ways, but keep getting errors ("Method 'SetSourceData' of object ....").

    Which is receommended: referencing the original data, or the pivot table (including/excluding column-headings/total rows)??


    • Edited by Red0256 Monday, April 10, 2017 7:46 PM
    Monday, April 10, 2017 7:34 PM
  • Try code like this:

    With ActiveSheet.PivotTables("PivotTable1")
        ActiveSheet.Shapes.AddChart.Select
        ActiveChart.ChartType = xlBarStacked
        ActiveChart.SetSourceData Source:=.TableRange1
    End With

    Tuesday, April 11, 2017 3:05 AM