Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
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! |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
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! |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
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! |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Automatic Update of Dropdown List Box data | Excel Worksheet Functions | |||
Data from two sheets make up a list in a third sheet (real time) | Excel Worksheet Functions | |||
auto updating list | Excel Worksheet Functions | |||
Compare 2 column list across 2 sheets | Excel Worksheet Functions | |||
Update master list with other lists | Excel Worksheet Functions |