VBA creating two same pie charts ona different worksheet of a same same workbook

Question

I want to create a pie chart for a pivot table.My pivot table is located at the same workbook in worksheet "New_Pivot".I want to create pie chart in the same workbook but different worksheet named "Pie_chart".I have created a code given below with formatting the size and background colour.My code is given below:

Sub Formattedchart()
'
' Formattedchart Macro
'

'
    Sheets("New_pivot").Select
    Range("A1:B16").Select

    ActiveSheet.Shapes.AddChart.Select
    ActiveChart.ChartType = xlPie

    ActiveChart.ShowValueFieldButtons = False

    Dim cht1 As Shape
    Set cht1 = ActiveSheet.Shapes(1)
    cht1.Name = "chart001"
    ActiveSheet.ChartObjects("chart001").Activate
    ActiveChart.SeriesCollection(1).Select
    ActiveChart.SeriesCollection(1).ApplyDataLabels




    ActiveSheet.ChartObjects("chart001").Activate
    ActiveSheet.Shapes("chart001").IncrementLeft -18
    ActiveSheet.Shapes("chart001").IncrementTop -125.25
    ActiveSheet.ChartObjects("chart001").Activate
    ActiveSheet.Shapes("chart001").ScaleWidth 0.9375, msoFalse, msoScaleFromTopLeft
    ActiveSheet.ChartObjects("chart001").Activate
    ActiveSheet.Shapes("chart001").ScaleHeight 1.73090296, msoFalse, _
        msoScaleFromTopLeft
    ActiveSheet.ChartObjects("chart001").Activate
    ActiveSheet.Shapes("chart001").ScaleHeight 1.1644936737, msoFalse, _
        msoScaleFromTopLeft
    ActiveSheet.ChartObjects("chart001").Activate
    ActiveSheet.Shapes("chart001").ScaleWidth 1.1377777778, msoFalse, _
        msoScaleFromTopLeft
    ActiveSheet.ChartObjects("chart001").Activate
    With ActiveSheet.Shapes("chart001").ThreeD
        .BevelTopType = msoBevelCircle
        .BevelTopInset = 6
        .BevelTopDepth = 6
    End With
    ActiveSheet.ChartObjects("chart001").Activate
    With ActiveSheet.Shapes("chart001").Fill
        .Visible = msoTrue
        .ForeColor.ObjectThemeColor = msoThemeColorAccent6
        .ForeColor.TintAndShade = 0
        .ForeColor.Brightness = 0.6000000238
        .Solid
    End With
    With ActiveSheet.Shapes("chart001").Fill
        .Visible = msoTrue
        .ForeColor.ObjectThemeColor = msoThemeColorAccent6
        .ForeColor.TintAndShade = 0
        .ForeColor.Brightness = 0.6000000238
        .Transparency = 0
        .Solid
    End With
    ActiveSheet.ChartObjects("chart001").Activate
    ActiveChart.SeriesCollection(1).Select
    ActiveChart.SeriesCollection(1).ChartType = xl3DPie
    ActiveChart.SeriesCollection(1).Select
    ActiveChart.SeriesCollection(1).ApplyDataLabels
    ActiveChart.ChartTitle.Select
    ActiveChart.ChartTitle.Text = "Consolidated"
    Selection.Format.TextFrame2.TextRange.Characters.Text = "Consolidated"
    With Selection.Format.TextFrame2.TextRange.Characters(1, 12).ParagraphFormat
        .TextDirection = msoTextDirectionLeftToRight
        .Alignment = msoAlignCenter
    End With
    With Selection.Format.TextFrame2.TextRange.Characters(1, 12).Font
        .BaselineOffset = 0
        .Bold = msoTrue
        .NameComplexScript = "+mn-cs"
        .NameFarEast = "+mn-ea"
        .Fill.Visible = msoTrue
        .Fill.ForeColor.RGB = RGB(0, 0, 0)
        .Fill.Transparency = 0
        .Fill.Solid
        .Size = 18
        .Italic = msoFalse
        .Kerning = 12
        .Name = "+mn-lt"
        .UnderlineStyle = msoNoUnderline
        .Strike = msoNoStrike
    End With

    ActiveSheet.ChartObjects("chart001").Activate
    ActiveChart.ChartTitle.Select
    Selection.Caption = "=New_pivot!R1C2"
End Sub

Unfortunately I am not be able to create the same chart in worksheet "Pie_chart" and also I need to create two same pie charts on this worksheet"Pie-Chart". I am new here so I am unable to perform this operation where I want to create two same pie charts on "Pie_chart" worksheet.

Some general advice: Wherever possible, [avoid using select](https://stackoverflow.com/questions/10714251/how-to-avoid-using-select-in-excel-vba), try setting your sizes/positions directly instead of using `Increment` and `Scale`, store your chart in a `Chart` or `ChartObject` object instead of a `Shape`, and be **very** careful when setting the Name of the object in code that you will run more than once. Oh, and wherever your chart is created, you can always use [`Chart.Move`](https://learn.microsoft.com/en-us/office/vba/api/excel.chart.move) to change that later. — Chronocidal, Sep 24 '18 at 08:28

VBA creating two same pie charts ona different worksheet of a same same workbook

0 Answers0