(Solved) – How to place multiple excel charts evenly in one chart sheet

  • by

Having used VBA code, I have been facing difficulty placing 4 chartobjects in single excel chartsheet with 4 ranges of data set. I managed to create all 4 charts in a chart sheet, but struggling to resize the first chartobject since it looks like the chart size is fixed ever. If I create a chart in a chart sheet, is it going to be the fixed size, fitting to the entire screen? Also, I tried to select each chart using chart index, but the first chartobject doesn’t have the index, resulting in getting only 3 index out of 4 chartobjects. Therefore, the code is not working since the second import chart will be index 1 which won’t match to ForLoop index. Can anybody advise on the problems?

    For i = 1 To cnt_dataset - 1

        Range((Cells(data_array(i, 1), 21)), (Cells(data_array(i, 2), 22))).Select
        ActiveChart.ChartType = xlLine
        ActiveChart.ApplyLayout (10)

        If i = 1 Then
            ActiveChart.Location Where:=xlLocationAsNewSheet, Name:="Chart"

            ActiveChart.Location Where:=xlLocationAsObject, Name:="Chart"

        End If

        With ActiveChart
            .ChartTitle.Text = "Chart A"
            .Axes(xlValue, xlPrimary).AxisTitle.Text = "y"
            .Axes(xlCategory, xlPrimary).AxisTitle.Text = "x"
        End With

        Selection.TickLabels.NumberFormat = "#,##0"
        ActiveWindow.WindowState = xlMaximized          

    Next i

Leave a Reply

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