Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Loop through specific worksheets
I am familiar with the code to loop through all the worksheets in a workbook
with a "For Each ws in Worksheets" construct but I would like a routine to be confined to a selection of named worksheets within a workbook. Let's say the worksheet names are in a range called "MyList" in a worksheet called "Control". What code should I use to apply my routine only to the worksheets in "Mylist". Thank you. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Loop through specific worksheets
Hi Ben,
Try something like: '<<============== Public Sub Tester02() Dim SH As Worksheet Dim rng As Range Dim rCell As Range Set rng = ActiveWorkbook.Sheets("Control").Range("MyList") For Each SH In ActiveWorkbook.Worksheets If Not IsError(Application.Match(SH.Name, rng, 0)) Then 'Do something, e.g.: MsgBox SH.Name End If Next SH End Sub '============== --- Regards, Norman "Ben" wrote in message ... I am familiar with the code to loop through all the worksheets in a workbook with a "For Each ws in Worksheets" construct but I would like a routine to be confined to a selection of named worksheets within a workbook. Let's say the worksheet names are in a range called "MyList" in a worksheet called "Control". What code should I use to apply my routine only to the worksheets in "Mylist". Thank you. |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Loop through specific worksheets
does this idea help? You do not usually have to specify the sheet name
unless you have duplicate names. Sub loopthrusheetarray() Dim Sh As Worksheet For Each Sh In Sheets(Array("Sheet2", "Sheet6", "Sheet10")) MsgBox Sh.Range("a1") Next End Sub -- Don Guillett SalesAid Software "Ben" wrote in message ... I am familiar with the code to loop through all the worksheets in a workbook with a "For Each ws in Worksheets" construct but I would like a routine to be confined to a selection of named worksheets within a workbook. Let's say the worksheet names are in a range called "MyList" in a worksheet called "Control". What code should I use to apply my routine only to the worksheets in "Mylist". Thank you. |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Loop through specific worksheets
Thank you to Don and Norman Both suggestions do the job.
"Don Guillett" wrote: does this idea help? You do not usually have to specify the sheet name unless you have duplicate names. Sub loopthrusheetarray() Dim Sh As Worksheet For Each Sh In Sheets(Array("Sheet2", "Sheet6", "Sheet10")) MsgBox Sh.Range("a1") Next End Sub -- Don Guillett SalesAid Software "Ben" wrote in message ... I am familiar with the code to loop through all the worksheets in a workbook with a "For Each ws in Worksheets" construct but I would like a routine to be confined to a selection of named worksheets within a workbook. Let's say the worksheet names are in a range called "MyList" in a worksheet called "Control". What code should I use to apply my routine only to the worksheets in "Mylist". Thank you. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Loop worksheets | Excel Discussion (Misc queries) | |||
Macro/Loop If Statement Help -delete the row with the specific te | Excel Programming | |||
Loop thru some worksheets | Excel Programming | |||
Loop Through worksheets | Excel Programming | |||
Loop worksheets | Excel Programming |