organizing embedded charts on 1 sheet

[PaulRuddy]

I have recently written a macro to measure prouctivity on a sales v labor basis, my problem is I cant figure out how to get the charts to organize themselves so I dont have to.

Basically at the moment the are embedding themselves on top of each other like this

but I need them to look like this on 1 page

any idea on how I can do this?

 

[PaulRuddy]

I fogot to say, I am using 2000 for this on a work based PC.

 

[Andrew Poulsom]

Look here:

http://peltiertech.com/Excel/ChartsHowTo/ResizeAndMoveAChart.html

 

[PaulRuddy]

I can see how that resizes one chart but how would I resize 8 charts and get them in the order I require on a 1 page sheet?

 

[Andrew Poulsom]

Doesn't this help?

Sub LineUpMyCharts()
    Dim MyWidth As Single, MyHeight As Single
    Dim NumWide As Long
    Dim iChtIx As Long, iChtCt As Long

    MyWidth = 200
    MyHeight = 150
    NumWide = 3

    iChtCt = ActiveSheet.ChartObjects.Count
    For iChtIx = 1 To iChtCt
        With ActiveSheet.ChartObjects(iChtIx)
            .Width = MyWidth
            .Height = MyHeight
            .Left = ((iChtIx - 1) Mod NumWide) * MyWidth
            .Top = Int((iChtIx - 1) / NumWide) * MyHeight
         End With
    Next
End Sub