Looping thru spreadsheets in a workbook
 

This one is a bit complicated and I understand if anyone
wants further clarification

Scenario

In an Excel workbook I have a login sheet which is hidden
in Column A of the login sheet I Have a list of all the
sheets in the workbook. ( say A1 thru A10)

Currently what happens is when the workbook opens I have a
piece of code which activates each individual sheet and
then carrys out some action. then it goes to the next
sheet and so on, this becomes a very lengthy process as
the code can be quite long

What I am looking for is code where it looks at the login
sheet grabs the value in Cell A1 and carry's out the
action then as long as there is a value in column A it
will keep refreshing the sheets, ie it moves to cell A2
passes the sheet name back to the code and continues on.
As soon as it comes across an empty value in Column A (ie
column A11) it stops and exits the code.

If you need further clarification please do not hesitate
to email me


Thanks in Advance

Nigel

Looping thru spreadsheets in a workbook
 

Assume the sheetname is Login

Dim cell as Range, rng as Range
dim sh as Worksheet
With worksheets("Login")
set rng = .range(.Cells(1,1),.Cells(1,1).End(xldown))
End with

for each cell in rng
set sh = worksheets(cell.Value)
msgbox sh.name
Next


--
Regards,
Tom Ogilvy

"Nigel Bennett" wrote in message
...
This one is a bit complicated and I understand if anyone
wants further clarification

Scenario

In an Excel workbook I have a login sheet which is hidden
in Column A of the login sheet I Have a list of all the
sheets in the workbook. ( say A1 thru A10)

Currently what happens is when the workbook opens I have a
piece of code which activates each individual sheet and
then carrys out some action. then it goes to the next
sheet and so on, this becomes a very lengthy process as
the code can be quite long

What I am looking for is code where it looks at the login
sheet grabs the value in Cell A1 and carry's out the
action then as long as there is a value in column A it
will keep refreshing the sheets, ie it moves to cell A2
passes the sheet name back to the code and continues on.
As soon as it comes across an empty value in Column A (ie
column A11) it stops and exits the code.

If you need further clarification please do not hesitate
to email me


Thanks in Advance

Nigel

Looping thru spreadsheets in a workbook
 

Thank you very much, that worked great, I do have one
other question

I would like to change the color of the active cell to
yellow as it loops thru so the user knows what sheet is
being activated

I tried

cell.Value.ForeColor = 0 ( I just put ) as I was not sure
of the code for yellow)

which doesn't work

any advice

Thanks
-----Original Message-----
Assume the sheetname is Login

Dim cell as Range, rng as Range
dim sh as Worksheet
With worksheets("Login")
set rng = .range(.Cells(1,1),.Cells(1,1).End(xldown))
End with

for each cell in rng
set sh = worksheets(cell.Value)
msgbox sh.name
Next


--
Regards,
Tom Ogilvy

"Nigel Bennett" wrote in message
...
This one is a bit complicated and I understand if anyone
wants further clarification

Scenario

In an Excel workbook I have a login sheet which is
hidden
in Column A of the login sheet I Have a list of all the
sheets in the workbook. ( say A1 thru A10)

Currently what happens is when the workbook opens I
have a
piece of code which activates each individual sheet and
then carrys out some action. then it goes to the next
sheet and so on, this becomes a very lengthy process as
the code can be quite long

What I am looking for is code where it looks at the
login
sheet grabs the value in Cell A1 and carry's out the
action then as long as there is a value in column A it
will keep refreshing the sheets, ie it moves to cell A2
passes the sheet name back to the code and continues on.
As soon as it comes across an empty value in Column A
(ie
column A11) it stops and exits the code.

If you need further clarification please do not hesitate
to email me


Thanks in Advance

Nigel



.

Going to a sheet when selecting a cell
 

On the list I would like also to select one of the cells
and have the focus go directly to the sheet in that cell
name

Going to a sheet when selecting a cell
 

You started out by saying

In an Excel workbook I have a login sheet which is hidden

I guess you meant it isn't hidden.

Dim cell as Range, rng as Range
dim sh as Worksheet
With worksheets("Login")
set rng = .range(.Cells(1,1),.Cells(1,1).End(xldown))
End with

for each cell in rng
cell.Activate
cell.Interior.ColorIndex = 6
set sh = worksheets(cell.Value)
msgbox sh.name
Next

--
Regards,
Tom Ogilvy


"Nigel Bennett" wrote in message
...
On the list I would like also to select one of the cells
and have the focus go directly to the sheet in that cell
name