i have used something almost exactly like tom posted. it selects the 1st
sheet, does the copy routine, selects the next sheet, does the copy routine
until it encounters the sheet called end, sheet4 it tom's post
would this work?

Sub copy_sheets()
Dim Sh As Worksheet
For Each Sh In Worksheets
Sh.Select
If Sh.Name = "Sheet4" Then Exit Sub
' enter your copy routine code here for each sheet
MsgBox Sh.Name ' just showing it selects the sheets before sheet4
Next
End Sub

--


Gary


"Big Al" wrote in message
...
I have a macro that selects a number of identical sheets and copies

and pastes special the data too all of them (Closing Bal to Open bal for

monthly roll). Currently the macro identifies each individual sheet

to select. If someone has added a new sheet in, then the macro needs to

be changed to add this new sheet.

Is there any way to select a range to sheets similar to the range

command?

Current Macro line

Sheets(Array("Product1","Product2","Product3","Pro duct4").select

Is there a command like

Sheets(Array("Product1:Product4")).Select

that could pick up any new sheets added between Product 1 and Product

4 sheets without manual intervention?


Cheers

Alan