We help IT Professionals succeed at work.

Macro Excel that create a pivot table and a chart on different sheets

mldaigle1 asked

Is it possible to create the pivot table on one sheet and the chart of the same data on a different sheet?
Below is my code for creating the pivot table... but i don't know how to create my chart from there...


Sub PivotType()

' Create pivot table for type of computers
    ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, _
        SourceData:=ActiveSheet.UsedRange, Version:=xlPivotTableVersion15). _
        CreatePivotTable TableDestination:="PivotTable!R2C1", TableName:= _
        "PivotTable1", DefaultVersion:=xlPivotTableVersion15
    Cells(2, 1).Select
    ActiveSheet.PivotTableWizard TableDestination:=ActiveSheet.Cells(2, 1)
    ActiveSheet.PivotTables("PivotTable1").AddFields RowFields:="Type"
    ActiveSheet.PivotTables("PivotTable1").PivotFields("Type").Orientation = xlDataField

    With ActiveSheet.PivotTables("PivotTable1").PivotFields("Type")
        .Orientation = xlRowField
        .Position = 1
    End With
    ActiveSheet.PivotTables("PivotTable1").CompactLayoutRowHeader = "Type"
    ActiveSheet.PivotTables("PivotTable1").DataPivotField.PivotItems( _
        "Count of Type").Caption = "Qty"
    With Selection
        .HorizontalAlignment = xlRight
        .VerticalAlignment = xlBottom
        .WrapText = False
        .Orientation = 0
        .AddIndent = False
        .IndentLevel = 0
        .ShrinkToFit = False
        .ReadingOrder = xlContext
        .MergeCells = False
    End With
Watch Question

Would it not be as simple as recording a macro like:

    ActiveChart.ChartType = xlColumnClustered ' Type
    ActiveChart.SetSourceData Source:=Range("Sheet1!$I$2:$J$6") ' Range Of Pivot

by simply selecting the pivot table and punching the F11 key?  In your case the pivot table might already be selected so you could just start macro recorder, hit F11 key, stop macro recorder, go to macro (should look something like above), and ten paste that code at the bottom of the macro creating the pivot table.  Can it be that easy?


Hi TomFarrar,

Well, you teach me something new.  I didn't know that trick F11 on a pivot table.  It is not bad, but I need to create 4 charts on the same sheet (the sheet name is "Dashboard").
Martin Liss"There is still no cure for the common birthday." ~John Glenn
Most Valuable Expert 2017
Distinguished Expert 2018

This question has been classified as abandoned and is closed as part of the Cleanup Program. See the recommendation for more details.

Hi mldaigle1 - My apology for not responding to your 3-27 email.  I had some immediate issues that required my attention.  My apology.  - Tom