Creating Graphs From Pivot Tables Using Macro

[3LydonsOnAShirt]

Hi there,

I'm fairly new to recording macro's. I've attempted to create one to consolidate some data, then create a pivot table from this, and lastly, create a graph from the pivot table.

The macro appears to work fine all the way through, until it comes to the Source Data for the Graph.

I've researched the errors & tried to play around with it but can't get it to work. The main error I'm getting is:

"Run time error '1004': Method 'Range' of object '_Global' failed"

My code (from Pivot creation to end) looks like this... I've coloured the errored line red:


Sheets.Add
ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:= _
"Consolidated Data!R1C1:R442C8", Version:=xlPivotTableVersion14). _
CreatePivotTable TableDestination:="", TableName:="PivotTable1" _
, DefaultVersion:=xlPivotTableVersion14
With ActiveSheet.PivotTables("PivotTable1").PivotFields("Category")
.Orientation = xlColumnField
.Position = 1
End With
With ActiveSheet.PivotTables("PivotTable1").PivotFields("Month")
.Orientation = xlRowField
.Position = 1
End With
ActiveSheet.PivotTables("PivotTable1").AddDataField ActiveSheet.PivotTables( _
"PivotTable1").PivotFields("Time"), "Sum of Time", xlSum
With ActiveSheet.PivotTables("PivotTable1").PivotFields("Sum of Time")
.Calculation = xlPercentOfRow
.NumberFormat = "0.00%"
End With
ActiveSheet.Shapes.AddChart.Select
ActiveChart.ChartType = xlColumnStacked
ActiveChart.SetSourceData Source:=Range("PivotTable1")
ActiveChart.Location Where:=xlLocationAsObject, Name:="Graph"
Sheets("Sheet1").Select
Sheets("Sheet1").Name = "Pivot"
Sheets("Pivot").Select
Sheets("Pivot").Move After:=Sheets(3)
ActiveWorkbook.Save
End Sub

The strange thing is, despite the errors, it still creates both the pivot & the graph, and they're correct, so I'm not sure the reason for the error?

Any suggestions would be greatly appreciated.