Loop through varible list of sheets
 

Hi all,

I'm trying to get excel to perform a certain macro on each sheet that
has the name of a entry in a list, eg; I have a list of names and all
of these names has their own sheet. This list is on a "Database" sheet
in a certain column. I want to run a macro on all the sheets with one
of those names, but not on my other sheets (which are used for
reporting). I've tried all sorts of loops, but I can not get this to
work

I hope my question is clear,

Thanks in advance and a happy new year!

Loop through varible list of sheets
 

Try this:

Sub test()
Dim shnames As Range
Set shnames = Range("A2:A5") 'adjust sheet name range!
For Each sh In shnames
Sheets(sh.Value).Select
MsgBox sh.Value
Call yourmacro
Next sh
End Sub

Regards,
Stefi


€� ezt Ã*rta:

Hi all,

I'm trying to get excel to perform a certain macro on each sheet that
has the name of a entry in a list, eg; I have a list of names and all
of these names has their own sheet. This list is on a "Database" sheet
in a certain column. I want to run a macro on all the sheets with one
of those names, but not on my other sheets (which are used for
reporting). I've tried all sorts of loops, but I can not get this to
work

I hope my question is clear,

Thanks in advance and a happy new year!

Loop through varible list of sheets
 

Hi Stefi,

It works great! Thanks, the only problem I have is at the end of the
Range, I get a "Subscript out of range" Message...I guess I have to end
the loop somehow?

Thanks again,
Wesley

Stefi wrote:
Try this:

Sub test()
Dim shnames As Range
Set shnames = Range("A2:A5") 'adjust sheet name range!
For Each sh In shnames
Sheets(sh.Value).Select
MsgBox sh.Value
Call yourmacro
Next sh
End Sub

Regards,
Stefi


" ezt írta:

Hi all,

I'm trying to get excel to perform a certain macro on each sheet that
has the name of a entry in a list, eg; I have a list of names and all
of these names has their own sheet. This list is on a "Database" sheet
in a certain column. I want to run a macro on all the sheets with one
of those names, but not on my other sheets (which are used for
reporting). I've tried all sorts of loops, but I can not get this to
work

I hope my question is clear,

Thanks in advance and a happy new year!