Thread: Charting data that needs to be consolidated?

Hi,

you can create a pivot table and pivot chart for that kind of work. Check out Pivot Table in the help files, and there are lots of threads in this forum, too.

cheers

Teylyn

Exaclty what I was looking to do. How in the world did I not think about pivotcharts!

Now, making that work as a macro is a new challenge.


It looks like I need to replace the references to sheet1 so that can dynamically change. If Sheet1 has already been created then it will error out and the create sheet command will create a sheet2 and then the macro will be lost.

Sub MakeBarChart()
'
' MakeBarChart Macro
'

'
    
    ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:= _
        "Sales&CommissionList!R5C1:R22C4", Version:=xlPivotTableVersion12). _
        CreatePivotTable TableDestination:="Sheet1!R1C1", TableName:="PivotTable1" _
        , DefaultVersion:=xlPivotTableVersion12
    Sheets.Add
    Cells(1, 1).Select
    ActiveSheet.Shapes.AddChart.Select
    ActiveChart.SetSourceData Source:=Range("Sheet1!$A$1:$C$18")
    ActiveWorkbook.ShowPivotChartActiveFields = True
    ActiveChart.ChartType = xlColumnClustered
    With ActiveSheet.PivotTables("PivotTable1").PivotFields("Sales Rep Name")
        .Orientation = xlRowField
        .Position = 1
    End With
    ActiveSheet.PivotTables("PivotTable1").AddDataField ActiveSheet.PivotTables( _
        "PivotTable1").PivotFields("Sales Amount"), "Sum of Sales Amount", xlSum
    ActiveWorkbook.ShowPivotChartActiveFields = False
End Sub

Solved! I can't figure out how to change thread title to say Solved.

Set TableDestination to "" and it auto puts it on a new sheet. Thanks for the help.

Sub MakeBarChart()
'
' MakeBarChart Macro
'

'
    
    ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:= _
        "Sales&CommissionList!R5C1:R22C4", Version:=xlPivotTableVersion12). _
        CreatePivotTable TableDestination:="", TableName:="PivotTable1" _
        , DefaultVersion:=xlPivotTableVersion12
    ActiveSheet.Shapes.AddChart.Select
  
    ActiveWorkbook.ShowPivotChartActiveFields = True
    ActiveChart.ChartType = xlColumnClustered
    With ActiveSheet.PivotTables("PivotTable1").PivotFields("Sales Rep Name")
        .Orientation = xlRowField
        .Position = 1
    End With
    ActiveSheet.PivotTables("PivotTable1").AddDataField ActiveSheet.PivotTables( _
        "PivotTable1").PivotFields("Sales Amount"), "Sum of Sales Amount", xlSum
    ActiveWorkbook.ShowPivotChartActiveFields = False
End Sub

Wow! All that VBA goes right over the top of my head , but I'm glad I put you on the right track

Solved! I can't figure out how to change thread title to say Solved.

Try to edit your original post and then look near the title where there should be a drop down box for "prefix". Here you can select "[Solved]". But you can only edit your original post for a limited time. After that, you can contact the mods and they can mark it for you.

cheers

Teylyn