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

Hi Alan,

I'm sure there should be a way of doing what you ask but I can't figure it!
In the meantime try this.

Sub Test()
Dim ws As Worksheet

For Each ws In ActiveWorkbook.Worksheets
If ws.Name = "Sheet1" Then
ws.Select
bSelect = True
Else
ws.Select False
If ws.Name = "Sheet3" Then Exit For
End If
Next

End Sub

Add sheets before Sheet1, between Sheet1 & Sheet3, and after Sheet3.
Hopefully all sheets between and incl Sheets 1 & 3 will be selected and no
others. Might want to test both Sheet1 & Sheet3 exist beforehand.

Regards,
Peter T

"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

Ignore the previous example

Sub Test2()
Dim bSelect As Boolean
Dim ws As Worksheet

For Each ws In ActiveWorkbook.Worksheets
If ws.Name = "Sheet3" And Not bSelect Then
Exit For 'Alert - Sheet3 is before Sheet1
ElseIf ws.Name = "Sheet1" Then
ws.Select
bSelect = True
Else
If bSelect Then
ws.Select False
If ws.Name = "Sheet3" Then Exit For
End If
End If
Next

End Sub

Regards,
Peter T

"Peter T" <peter_t@discussions wrote in message
...
Hi Alan,

I'm sure there should be a way of doing what you ask but I can't figure
it!
In the meantime try this.

Sub Test()
Dim ws As Worksheet

For Each ws In ActiveWorkbook.Worksheets
If ws.Name = "Sheet1" Then
ws.Select
bSelect = True
Else
ws.Select False
If ws.Name = "Sheet3" Then Exit For
End If
Next

End Sub

Add sheets before Sheet1, between Sheet1 & Sheet3, and after Sheet3.
Hopefully all sheets between and incl Sheets 1 & 3 will be selected and no
others. Might want to test both Sheet1 & Sheet3 exist beforehand.

Regards,
Peter T

"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

If you could be sure that the new sheets always started with "Product" (and you
wanted all the worksheets that start with Product), you could loop through the
worksheets looking at their names.

If you really want the worksheets that are trapped between Product1 and
Product2, you could do something like:

Option Explicit
Sub testme()
Dim LowerIndex As Long
Dim UpperIndex As Long
Dim wks As Worksheet
Dim myNames() As String
Dim temp As Long
Dim nCtr As Long

ReDim myNames(1 To 2)
myNames(1) = "product1"
myNames(2) = "product4"

LowerIndex = Worksheets(myNames(1)).Index
UpperIndex = Worksheets(myNames(2)).Index

If LowerIndex UpperIndex Then
temp = LowerIndex
LowerIndex = UpperIndex
UpperIndex = temp
End If

nCtr = 2
For Each wks In ActiveWorkbook.Worksheets
If wks.Index LowerIndex _
And wks.Index < UpperIndex Then
nCtr = nCtr + 1
ReDim Preserve myNames(1 To nCtr)
myNames(nCtr) = wks.Name
End If
Next wks

Worksheets(myNames).Select

'but you don't always have to select sheets to work with them.
For Each wks In Worksheets(myNames)
MsgBox wks.Name
Next wks

End Sub

Big Al wrote:

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

--

Dave Peterson

Trapped between product1 and product4!!

If you really want the worksheets that are trapped between Product1 and
Product2, you could do something like:

<<snipped

Sub Test2()
Dim Sh As Worksheet
For Each sh In Worksheets
sh.Select (sh.Name = "Sheet2")
If sh.Name = "Sheet4" Then Exit Sub
Next
End Sub

Would be another possibility (where the sheet represented by Sheet2
precedes the sheet represented by Sheet4 in the tab order)

--
Regards,
Tom Ogilvy


"Peter T" <peter_t@discussions wrote in message
...
Ignore the previous example

Sub Test2()
Dim bSelect As Boolean
Dim ws As Worksheet

For Each ws In ActiveWorkbook.Worksheets
If ws.Name = "Sheet3" And Not bSelect Then
Exit For 'Alert - Sheet3 is before Sheet1
ElseIf ws.Name = "Sheet1" Then
ws.Select
bSelect = True
Else
If bSelect Then
ws.Select False
If ws.Name = "Sheet3" Then Exit For
End If
End If
Next

End Sub

Regards,
Peter T

"Peter T" <peter_t@discussions wrote in message
...
Hi Alan,

I'm sure there should be a way of doing what you ask but I can't figure
it!
In the meantime try this.

Sub Test()
Dim ws As Worksheet

For Each ws In ActiveWorkbook.Worksheets
If ws.Name = "Sheet1" Then
ws.Select
bSelect = True
Else
ws.Select False
If ws.Name = "Sheet3" Then Exit For
End If
Next

End Sub

Add sheets before Sheet1, between Sheet1 & Sheet3, and after Sheet3.
Hopefully all sheets between and incl Sheets 1 & 3 will be selected and
no
others. Might want to test both Sheet1 & Sheet3 exist beforehand.

Regards,
Peter T

"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

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