VBA Excel to Create Chart on a particular location of a particular worksheet

Question

I have a code which creates charts.I want to create this chart on a specific worksheet named as "Charts" with a particular location A5:F18.My code is given below:

Sub Macro1()
    Dim i As Long

        Range("B5:E5").Offset(i).Select
        With ActiveSheet.Shapes.AddChart
            With .Chart
                .ChartType = xlColumnClustered
                .SetSourceData Source:=Range("Pivot!$A$3:$E$5").Offset(i)
                .SeriesCollection(1).ApplyDataLabels
                .SeriesCollection(2).ApplyDataLabels
                .SeriesCollection(3).ApplyDataLabels
                .ShowValueFieldButtons = False
                .HasTitle = True
                .ChartTitle.Text = "Consolidated"

                End With
                .Name = "chart" & Format(i + 1, "000")
                .Width = 288
                .LockAspectRatio = msoTrue
            End With
End Sub

I am not be able to create that chart on that particular worksheet with this particular location. How to do that?

Answer 1

you can try this:

    With ActiveSheet.Shapes.AddChart
        With .Chart
            .ChartType = xlColumnClustered
            .SetSourceData Source:=Range("Pivot!$A$3:$E$5").Offset(i)
            .SeriesCollection(1).ApplyDataLabels
            .SeriesCollection(2).ApplyDataLabels
            .SeriesCollection(3).ApplyDataLabels
            .ShowValueFieldButtons = False
            .HasTitle = True
            .ChartTitle.Text = "Consolidated"
        End With

        .Name = "chart" & Format(i + 1, "000")
        .Top = Range("Pivot!$A$5:$F$18").Top
        .Left = Range("Pivot!$A$5:$F$18").Left
        .Width = Range("Pivot!$A$5:$F$18").Width
        .Height = Range("Pivot!$A$5:$F$18").Height
        .LockAspectRatio = msoTrue
    End With