We help IT Professionals succeed at work.

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

mldaigle1
mldaigle1 asked
on
Hi,

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
    Sheets("Inventaire").Select
    ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, _
        SourceData:=ActiveSheet.UsedRange, Version:=xlPivotTableVersion15). _
        CreatePivotTable TableDestination:="PivotTable!R2C1", TableName:= _
        "PivotTable1", DefaultVersion:=xlPivotTableVersion15
   
    Sheets("PivotTable").Select
    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
   
    Range("A2").Select
    ActiveSheet.PivotTables("PivotTable1").CompactLayoutRowHeader = "Type"
    Range("B2").Select
    ActiveSheet.PivotTables("PivotTable1").DataPivotField.PivotItems( _
        "Count of Type").Caption = "Qty"
    Range("B2").Select
    With Selection
        .HorizontalAlignment = xlRight
        .VerticalAlignment = xlBottom
        .WrapText = False
        .Orientation = 0
        .AddIndent = False
        .IndentLevel = 0
        .ShrinkToFit = False
        .ReadingOrder = xlContext
        .MergeCells = False
    End With
Comment
Watch Question

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

Charts.Add
    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?

Author

Commented:
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

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

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