Thread: Rearranging many charts at once

Hi there,

I'm looking for a method of rearranging many charts at once in the same worksheet. I have about 90 charts created that present performance of 90 various airports. And every month each airport performs better or worse and this is why I need to rearrange these charts every month (because I present them starting from the best one to the worst one for the selected month).

How to do it? I have no idea. I've seen various VBA macros that resize and align all charts at once, but I've never come across any that rearrange them according to some criteria...

please help
thanks

Originally Posted by kapiszon2929

Hi there,

I'm looking for a method of rearranging many charts at once in the same worksheet. I have about 90 charts created that present performance of 90 various airports. And every month each airport performs better or worse and this is why I need to rearrange these charts every month (because I present them starting from the best one to the worst one for the selected month).

How to do it? I have no idea. I've seen various VBA macros that resize and align all charts at once, but I've never come across any that rearrange them according to some criteria...

please help
thanks

Hi,

I have exactly the same requirement for our Investment Club's charts, and devised the following Macro to do this. You might need to make a few changes to fit your layout, but hopefully it will make some sense.

The charts are covered by a range of cells named 'chartlist' which you'll see in the code. This range is a 3 column table where column 1 is numbered 1,2,3 etc. column 2 is a reference to the name of the chart and column 3 is the Excel number associated with the name for the chart in question. i.e. the name you see in the XL name box when you CTRL-Shift and click on the. e.g. if the chart is named 'Chart 106', the number in the table is 106.

The macro aligns the charts in pairs side by side with small gaps between and below. The sizes of the charts are hard coded, i.e. height 230, width 500 which you may want to change.

You can leave out the last four lines in the 'y' loop, beginning Range("S37.... if you like. This just lists the chart position numbers in a separate area.

Sub PositionCharts()
Dim x As Integer
Dim y As Integer
Dim obChart As String
Dim iChartNo As Integer: Dim iTop As Integer: Dim iLeft As Integer

x = Range("chartlist").Rows.Count
iTop = 200

Range("A25").Select

For y = 0 To x - 1
    iChartNo = Application.WorksheetFunction.VLookup(y + 1, Range("chartlist"), 3)
    obChart = "Chart " & iChartNo
   
If y < Int(x / 2) + 1 Then
        iLeft = 30
        iTop = iTop + 235
End If

If y = Int(x / 2) + 1 Then iTop = 200
If y >= Int(x / 2) + 1 Then
        iLeft = 570
       iTop = iTop + 235
End If
Worksheets("Charts").ChartObjects(obChart).Height = 230
Worksheets("Charts").ChartObjects(obChart).Width = 500
Worksheets("Charts").ChartObjects(obChart).Top = iTop
Worksheets("Charts").ChartObjects(obChart).Left = iLeft

    Range("S37").Offset(y, 0) = y
    Range("S37").Offset(y, 1) = iTop
    Range("S37").Offset(y, 2) = iLeft
    Range("S37").Offset(y, 3) = obChart
Next y
    
End Sub

Hope it offers some ideas.

Rgds

Many thanks Richard for your reply, I tried to use your example and modify it to match the needs of my file but unfortunately couldn't do it. I'm still too amateur in VBA programming to work it out.

Could you send to me the file that you are using in zip format? I'd like to have a look at it and in such way I could understand better how your file structured is.

I hope that you're gonna be able to share it with me. If you want, you may change the figures to unreal values - I just simply want to analyze the charts and the macro.

Thanks in advance, best regards,