(Solved) – how do i offset all the charts in the same worksheet in VBA?


Currently, all my graphs are cramped together in the same spot in the same worksheet after running my code. So to view them i have to manually drag and move them to another spot. So is there a way such that i can place all the charts in a orderly manner as shown in expected output? If it is really impossible to do something like this, i am ok with offsetting the graph for every 20 cells even though it is abit inconvenient for viewing but still i attempted to do it but fail to make it happen when i include code with current output with the offsetting code.

Current output

enter image description here

Below is the code for my current output

Sub plotgraphs()

'Call meangraph
Call sigmagraph

End Sub
Private Sub sigmagraph()
    Dim i As Long, c As Long
    Dim shp As Shape
    Dim Cht As chart, co As Shape
    Dim rngDB As Range, rngX As Range, rngY As Range

    Dim Srs As Series
    Dim ws As Worksheet

    Set ws = Sheets("Data")

    Set rngDB = ws.Range("A1").CurrentRegion

    Set rngX = rngDB.Columns(1)
    Set rngY = rngDB.Columns(4)

    Do While Application.CountA(rngY) > 0

        Set co = Worksheets("meangraphs").Shapes.AddChart
        Set Cht = co.chart

        With Cht
            .ChartType = xlXYScatter
            'remove any data which might have been
            '  picked up when adding the chart
            Do While .SeriesCollection.Count > 0
            'add the data
            With .SeriesCollection.NewSeries()
                .XValues = rngX.Value
                .Values = rngY.Value
            End With
            With Cht.Axes(xlValue)
                .MinimumScale = 0
                .MaximumScale = 0.5
                .TickLabels.NumberFormat = "0.00E 00"
            End With
            Cht.Axes(xlCategory, xlPrimary).HasTitle = True
            Cht.Axes(xlValue, xlPrimary).HasTitle = True
        End With

        Set rngY = rngY.Offset(0, 2) 'next y values


Code for offsetting chart for every 20 cells (fail to make it happen)

 Dim OutSht As Worksheet
   Dim PlaceInRange As Range

    Set OutSht = ActiveWorkbook.Sheets("sigmagraphs") '<~~ Output sheet
   Set PlaceInRange = OutSht.Range("B2:J21")        '<~~ Output location

'    To place charts at a distance between them
    For Each chart In Sheets("sigmagraphs").ChartObjects
'        OutSht.Paste PlaceInRange
'        Code below changes the range itself to something 20 rows below
        Set PlaceInRange = PlaceInRange.Offset(20, 0)
   Next chart

Expected output

enter image description here

Leave a Reply

Your email address will not be published. Required fields are marked *