selecting multiple sheets in a macro

[Big Al]

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","Product4").select

Is there a command like

Sheets(Array("Product1roduct4")).Select

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

4 sheets without manual intervention?


Cheers

Alan

 

[Peter T]

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

 

[Peter T]

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

 

[Dave Peterson]

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

 

[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>>

 

[Tom Ogilvy]

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)

 

[Gary Keramidas]

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