Arrange graphs orderly in worksheet using vba

Question

I want to arrange the 8 charts that I pasted in a destination worksheet (from two destination ws).
How can I oganize the charts such that they paste next to each other (left top:L7) in two rows? I have 2 times 4 charts in my "source" ws, but the last chart seems to be missing at the destination ws when I run the macro (so I actually only have 7 charts)

Thank you

    Dim OutSht As Worksheet
    Dim Chart As ChartObject
    Dim PlaceInRange As Range

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

    'Loop charts
    For Each Chart In Sheets("Output").ChartObjects
        'Copy/paste charts
        Chart.Cut
        OutSht.Paste PlaceInRange
    Next Chart

    For Each Chart In Sheets("Uddybet").ChartObjects
        'Copy/paste charts
        Chart.Cut
        OutSht.Paste PlaceInRange
    Next Chart

Answer 1

I am not quite sure if this is what you are looking for!

I looked for the cells where the charts are and then set the next chart position according to these.

It's possible to simplify the code, but I leave it to you!

Sub getCharts()
    Dim wsOutp As Worksheet: Set wsOutp = ActiveWorkbook.Sheets("Guide")
    Dim wsSrc1 As Worksheet: Set wsSrc1 = ActiveWorkbook.Sheets("Output")
    Dim wsSrc2 As Worksheet: Set wsSrc2 = ActiveWorkbook.Sheets("Uddybet")
    Dim x As Object
    
    Dim xTopLeftCellRow As Long, xBottomRightCellRow As Long
    Dim xTopLeftCellCol As Long, xBottomRightCellCol As Long
    Dim xDiffCols As Long
    Dim xRng As Range

    wsOutp.Select
    
    Dim aCell As Range: Set aCell = wsOutp.[B2]
    aCell.Activate
    
    ' Loop on sheet Output
    For Each x In wsSrc1.ChartObjects
        xTopLeftCellRow = x.TopLeftCell.Row
        xTopLeftCellCol = x.TopLeftCell.Column

        xBottomRightCellRow = x.BottomRightCell.Row
        xBottomRightCellCol = x.BottomRightCell.Column

        xDiffCols = xBottomRightCellCol - xTopLeftCellCol + 1
        
        ' Chart range
        Set xRng = Range(Cells(xTopLeftCellRow, xTopLeftCellCol), Cells(xBottomRightCellRow, xBottomRightCellCol))
                
        ' Move Chart
        x.Cut
        ActiveSheet.Paste
        
        ' Next chart position
        Set aCell = aCell.Offset(0, xDiffCols)
        aCell.Activate
    Next
    
    ' Loop on sheet Uddybet
    For Each x In wsSrc2.ChartObjects
        xTopLeftCellRow = x.TopLeftCell.Row
        xTopLeftCellCol = x.TopLeftCell.Column

        xBottomRightCellRow = x.BottomRightCell.Row
        xBottomRightCellCol = x.BottomRightCell.Column

        xDiffCols = xBottomRightCellCol - xTopLeftCellCol + 1
        
        ' Chart range
        Set xRng = Range(Cells(xTopLeftCellRow, xTopLeftCellCol), Cells(xBottomRightCellRow, xBottomRightCellCol))
                
        ' Move Chart
        x.Cut
        ActiveSheet.Paste
        
        ' Next chart position
        Set aCell = aCell.Offset(0, xDiffCols)
        aCell.Activate
    Next
End Sub