All charts in same sheet

[maxtin]

Hi to all,
I have an Excel file with 4 different sheets in it. Each of those sheet contains a chart and i would like to put all those charts in the same one sheet...in a way that i can see all charts (eg. 1/4 page for each chart in this case). I would also have the code to be flexible and work for n number of charts.

TIA

[Bob Phillips]

Are they in chartsheets or worksheets?

How about a different approach, a list on one sheet, and copy in the selected chart?

[maxtin]

they are worksheets....sorry i dont get your list idea

[Bob Phillips]

Sub ChartEm()
Dim mpCharts As Long
Dim mpCols As Long
Dim mpWinWidth As Double
Dim mpWinHeight As Double
Dim mpSheet As Worksheet
Dim mpChartObj As ChartObject
Dim mpLeft As Double
Dim mpTop As Double
Dim mpRightStep As Double
Dim mpDownStep As Double
Dim i As Long, j As Long

mpWinWidth = ActiveWindow.Width - 50
mpWinHeight = ActiveWindow.Height - 50

With ActiveSheet


For Each mpSheet In ActiveWorkbook.Worksheets

If mpSheet.Name <> .Name Then

Set mpChartObj = Nothing
On Error Resume Next
Set mpChartObj = mpSheet.ChartObjects(1)
On Error Resume Next

If Not mpChartObj Is Nothing Then

mpChartObj.Cut
.Paste
ActiveChart.Parent.Left = 25
ActiveChart.Parent.Top = 25
mpCharts = mpCharts + 1
ActiveChart.Name = "chart_" & mpCharts
End If

mpCols = Sqr(mpCharts)
If mpCols ^ 2 <> mpCharts Then

mpCols = Application.RoundUp(mpCols, 0)
End If
End If
Next mpSheet

mpTop = 15
mpLeft = 15
mpRightStep = mpWinWidth / mpCols
mpDownStep = mpWinHeight / Application.RoundUp(mpCharts / mpCols, 0)
For i = 1 To mpCharts Step mpCols

For j = 1 To mpCols

If (i \ 3) * mpCols + j > mpCharts Then Exit For

.ChartObjects((i \ 3) * mpCols + j).Left = mpLeft
.ChartObjects((i \ 3) * mpCols + j).Width = mpRightStep - 10
.ChartObjects((i \ 3) * mpCols + j).Top = mpTop
.ChartObjects((i \ 3) * mpCols + j).Height = mpDownStep - 10
mpLeft = mpLeft + mpRightStep
Next j

mpLeft = 15
mpTop = mpTop + mpDownStep
Next i
End With
End Sub