Problems ending a loop

[steve_bris]

Hi.

This code is to take a excel file...... which has a lot of of sheets called chart1, chart2 etc which have charts on them ...then sheet1 -3 at the end.

My code coppies all the sharts and puts them on the front pare that I have created ok, but it gets an error and won't stop the look when it has coppied the last chart ( there will always be an even number of charts)

Any ideas how to avoid the error of looking for the chart on the first sheet page ?

Thanks heaps for any help

ExcelApp.Workbooks.Open AppPath & "\first.xls"
ExcelApp.Visible = True
Sheets("Chart1").Select
ExcelApp.Worksheets.Add
ActiveSheet.Name = "Summary Page"

Do Until ActiveSheet.Name = "sheet1"
Sheets("Chart" & intCrtNo).Select <------gets an error here when the charts finish and the "sheet1" sheet is looked at erroneously.
ActiveChart.ChartArea.Copy
Sheets("Summary Page").Select
Range("A" & rngA).Select
ActiveSheet.Paste
ActiveSheet.Shapes("Chart" & " " & intCrtNo).ScaleWidth 0.5, msoFalse, msoScaleFromTopLeft
ActiveSheet.Shapes("Chart" & " " & intCrtNo).ScaleHeight 0.5, msoFalse, msoScaleFromTopLeft
intCrtNo = intCrtNo + 1
rngA = rngA + 20
Sheets("Chart" & intCrtNo).Select
ActiveChart.ChartArea.Copy
Sheets("Summary Page").Select
Range("J" & rngJ).Select
ActiveSheet.Paste
ActiveSheet.Shapes("Chart" & " " & intCrtNo).ScaleWidth 0.5, msoFalse, msoScaleFromTopLeft
ActiveSheet.Shapes("Chart" & " " & intCrtNo).ScaleHeight 0.5, msoFalse, msoScaleFromTopLeft
intCrtNo = intCrtNo + 1
rngJ = rngJ + 20
Loop

ExcelApp.ActiveWorkbook.SaveAs AppPath & "\first.xls"
ExcelApp.ActiveWorkbook.Close

 

[filo65]

Hi,

why don't you try with something like

Dim sh as worksheet

for each sh in worksheets
if UCase(sh.name) = "SHEET1" then Exit For
...
...
Next

HTH

filo65

 

[steve_bris]

Thanks for the reply Filo.

I tried it and it didn't produce an error but it didn't loop, so it only coppied the first 2 charts.

Any idea how to make it loop

Thanks



For Each sh In Worksheets
If UCase(sh.Name) = "SHEET1" Then Exit For

'Do Until ActiveSheet.Name = "Sheet1"
Sheets("Chart" & intCrtNo).Select
ActiveChart.ChartArea.Copy
Sheets("Summary Page").Select
Range("A" & rngA).Select
ActiveSheet.Paste
ActiveSheet.Shapes("Chart" & " " & intCrtNo).ScaleWidth 0.5, msoFalse, msoScaleFromTopLeft
ActiveSheet.Shapes("Chart" & " " & intCrtNo).ScaleHeight 0.5, msoFalse, msoScaleFromTopLeft
intCrtNo = intCrtNo + 1
rngA = rngA + 20
Sheets("Chart" & intCrtNo).Select
ActiveChart.ChartArea.Copy
Sheets("Summary Page").Select
Range("J" & rngJ).Select
ActiveSheet.Paste
ActiveSheet.Shapes("Chart" & " " & intCrtNo).ScaleWidth 0.5, msoFalse, msoScaleFromTopLeft
ActiveSheet.Shapes("Chart" & " " & intCrtNo).ScaleHeight 0.5, msoFalse, msoScaleFromTopLeft
intCrtNo = intCrtNo + 1
rngJ = rngJ + 20
'Loop
Next

 

[steve_bris]

Problem solved......thanks for the help