Macros to select multiple sheets to make simultaneous change

N

noel

Hi,

I need to have a macro such that it'll select all sheets
from the first to the sheet before a sheet called "test".
After selecting these sheets, i want to make simultaneous
changes to them. So how do I add the code in the macro to
choose all these sheets?

I can't put the names of the sheets coz the names changes
often.

THanks!
noel
 
R

Ron de Bruin

Hi noel

This will loop through the sheets

Sub test()
Dim num As Integer
Dim a As Integer
num = Sheets("test").Index - 1
For a = 1 To num
With Sheets(a)
'.Select
MsgBox .Name
End With
Next
End Sub

If you want to select them try this

Sub test2()
Dim a As Integer
Dim arr() As String
Dim N As Integer
Dim Num As Integer

With ThisWorkbook
Num = .Sheets("test").Index - 1
N = 0
For a = 1 To Num
If .Sheets(a).Visible = True Then
N = N + 1
ReDim Preserve arr(1 To N)
arr(N) = .Sheets(a).Name
End If
Next
.Worksheets(arr).Select
End With
End Sub
 
D

Dave O

Hi, Noel-
The following code will perform an action in each tab of your sprdsht:

For Each SheetName in Sheets
{your code here}
Next SheetName

Because this performs {your code here} in EVERY tab you may need to
write some code in that avoids certain tabs, which would look like
this:

For Each SheetName in Sheets
if SheetName.Name = "Disregard this tab" then GoTo Bailout:

{Your code here}

Bailout:
Next SheetName

Hope this helps!
 
Top