Results 1 to 2 of 2
  1. #1
    New Lounger
    Join Date
    Dec 2012
    Thanked 0 Times in 0 Posts

    Cannot create Pivot Chart with VBA in 2007

    Initally I found that creating a pivot table was a problem until I found something on Microsoft which suggested emptying the value of the TableDestination variable (tableDestination="") and removing the following lines from the code Sheets.add and Sheets ("sheetname").Select Cells(x,y).Select. Which incidentally worked a treat.

    However, the powers that be want a Pivot Chart - again I have found I can alter the same bits of code but now run into a problem with the row

    ActiveChart.SetSourceData Source:=Range("'Sheet17'!$A$1:$C$18")

    Run time error '1004'. Method 'range' of object'_Global Failed

    I suspect this is a similar problem but don't know how to resolve it. I am also going to have an issues that I intend either using a table format or a dynamic range as although the workbook and sheet will always have the same name - the number of rows are variable

    I have tried copying the code from my Personal macro file to the workbook itself - but when I run it there I get an error 400?!!

    the code is : (I have taken the liberty of attaching an example file)

    Sub MacroPivotChart()

    ' To create a Pivot Chart instead of the Table
    ActiveWorkbook.PivotCaches.Create(SourceType:=xlDa tabase, SourceData:= _
    "Log Data!R1C1:R233C4", Version:=xlPivotTableVersion12).CreatePivotTable _
    TableDestination:="", TableName:="PivotTable4", DefaultVersion:=xlPivotTableVersion12
    'this is where it fails
    ActiveChart.SetSourceData Source:=Range("'Sheet17'!$A$1:$C$18")
    ActiveWorkbook.ShowPivotChartActiveFields = True
    ActiveChart.ChartType = xlColumnClustered
    ActiveSheet.PivotTables("PivotTable4").AddDataFiel d ActiveSheet.PivotTables( _
    "PivotTable4").PivotFields("MINUTE"), "Sum of MINUTE", xlSum
    With ActiveSheet.PivotTables("PivotTable4").PivotFields ("Sum of MINUTE")
    .Orientation = xlPageField
    .Position = 1
    End With
    With ActiveSheet.PivotTables("PivotTable4").PivotFields ("Date/Time")
    .Orientation = xlRowField
    .Position = 1
    End With
    ActiveSheet.PivotTables("PivotTable4").AddDataFiel d ActiveSheet.PivotTables( _
    "PivotTable4").PivotFields("(+)Total (L)"), "Sum of (+)Total (L)", xlSum
    ActiveSheet.PivotTables("PivotTable4").AddDataFiel d ActiveSheet.PivotTables( _
    "PivotTable4").PivotFields("Volume (L/m)"), "Sum of Volume (L/m)", xlSum
    ActiveChart.ChartType = xlLineMarkers
    ActiveSheet.PivotTables("PivotTable4").PivotFields ("MINUTE").ClearAllFilters
    ActiveSheet.PivotTables("PivotTable4").PivotFields ("MINUTE").CurrentPage = "0"chelt log (2).xlsx

    many thanks

  2. #2
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Burwash, East Sussex, United Kingdom
    Thanked 215 Times in 198 Posts
    ActiveChart.SetSourceData Source:=Sheets("Sheet17").Range("A1:C18")

    Microsoft MVP - Excel

Posting Permissions

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