Format All Graphs

[Pestomania]

Hi there!!

I have 30+ graphs on a "dashboard" that keep getting formatted wrong whenever a row is added or deleted.

Is there a way to make all graphs the same size (2.95" x 4.48") and put a 0.025" space in the vertical with a 0.005" space in the horizontal between each graph.

 

[John_w]

Try this macro. You just have to edit the code where indicated to specify the name of the 'Dashboard' sheet and the cell where the first chart should be positioned.

Public Sub Position_Charts()

    Dim DashboardSheet As Worksheet
    Dim c As Long
    Dim chart1Left As Double
    Dim chartTop As Double, chartLeft As Double, chartWidth As Double, chartHeight As Double
    Dim verticalGap As Double, horizontalGap As Double
    
    chartWidth = Application.InchesToPoints(4.48)
    chartHeight = Application.InchesToPoints(2.95)
    verticalGap = Application.InchesToPoints(0.025)
    horizontalGap = Application.InchesToPoints(0.005)
    
    'Define Dashboard sheet
    Set DashboardSheet = ThisWorkbook.Worksheets("Charts")  'SPECIFY SHEET NAME
    
    'Define position of first chart
    With DashboardSheet.Range("D4")                         'SPECIFY CELL
        chartTop = .Top
        chartLeft = .Left
    End With
    
    chart1Left = chartLeft
    
    With DashboardSheet
        For c = 1 To .ChartObjects.Count
            .ChartObjects(c).Top = chartTop
            .ChartObjects(c).Left = chartLeft
            .ChartObjects(c).Width = chartWidth
            .ChartObjects(c).Height = chartHeight
            If c Mod 2 = 0 Then
                '2 charts per row - next row
                chartTop = chartTop + chartHeight + horizontalGap
                chartLeft = chart1Left
            Else
                chartLeft = chartLeft + chartWidth + verticalGap
            End If
        Next
    End With
    
End Sub