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.
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 .SeriesCollection(1).Delete Loop 'add the data With .SeriesCollection.NewSeries() .XValues = rngX.Value .Values = rngY.Value End With 'formatting... 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 Loop
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