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

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
mldaigle1Asked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

tomfarrarCommented:
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?

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
mldaigle1Author 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 LissOlder than dirtCommented:
This question has been classified as abandoned and is closed as part of the Cleanup Program. See the recommendation for more details.
tomfarrarCommented:
Hi mldaigle1 - My apology for not responding to your 3-27 email.  I had some immediate issues that required my attention.  My apology.  - Tom
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Visual Basic Classic

From novice to tech pro — start learning today.