Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 . |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Maximunm # of Spreadsheets in any Workbook | New Users to Excel | |||
creating a looping formula between different spreadsheets | New Users to Excel | |||
looping a calculation until workbook closes | Excel Worksheet Functions | |||
macro to browse for workbook, pick up data and looping | Excel Worksheet Functions | |||
Looping Through Worksheets In A Workbook | Excel Programming |