Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Putting Sheet Names on a list
HI!
Can anybody please tell me how to put the list of Sheet Names in a list. Like for example, in a particular cell, a list would show "Sheet1", "Sheet2", & "Sheet3". Would it be possible also, that if a new Sheet will be added, ie. "Sheet4", it would just add up on that list. That list will be then used as choices to what particular sheet I would print. The situation is, I have a table on each sheet. Same format. Sometimes, new sheet/s is/are added. Within that table, I have series of codes taking data in one row, pasting it in a form, and give print. Then, goes to the next row...and so on and so forth. Please help. Thanks a lot people of great minds..... Best regards. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Putting Sheet Names on a list
There is no worksheet add event to trap to update the list, so you have a
problem. One way to do it might be to use OnTime to run every say 10 secs and rebuild the list, but it would be resource hungry. -- HTH RP (remove nothere from the email address if mailing direct) "arcq" wrote in message oups.com... HI! Can anybody please tell me how to put the list of Sheet Names in a list. Like for example, in a particular cell, a list would show "Sheet1", "Sheet2", & "Sheet3". Would it be possible also, that if a new Sheet will be added, ie. "Sheet4", it would just add up on that list. That list will be then used as choices to what particular sheet I would print. The situation is, I have a table on each sheet. Same format. Sometimes, new sheet/s is/are added. Within that table, I have series of codes taking data in one row, pasting it in a form, and give print. Then, goes to the next row...and so on and so forth. Please help. Thanks a lot people of great minds..... Best regards. |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Putting Sheet Names on a list
I use the following often; It's not exactly what your asking
but I find it helpful - and maybe you can use it to get to your solution In a standard module paste in: Sub ListOutSheetNames() Application.ScreenUpdating = False Dim Nsheet As Worksheet Set Nsheet = Sheets.Add Dim WS As Worksheet Dim r As Integer r = 1 For Each WS In Worksheets If WS.Name < Nsheet.Name Then Nsheet.Range("A" & r) = WS.Name r = r + 1 End If Next WS Application.DisplayAlerts = False Application.DisplayAlerts = True Application.ScreenUpdating = True End Sub Works well for me.. HTH "arcq" wrote in message oups.com... HI! Can anybody please tell me how to put the list of Sheet Names in a list. Like for example, in a particular cell, a list would show "Sheet1", "Sheet2", & "Sheet3". Would it be possible also, that if a new Sheet will be added, ie. "Sheet4", it would just add up on that list. That list will be then used as choices to what particular sheet I would print. The situation is, I have a table on each sheet. Same format. Sometimes, new sheet/s is/are added. Within that table, I have series of codes taking data in one row, pasting it in a form, and give print. Then, goes to the next row...and so on and so forth. Please help. Thanks a lot people of great minds..... Best regards. |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Putting Sheet Names on a list
Jim May wrote: I use the following often; It's not exactly what your asking but I find it helpful - and maybe you can use it to get to your solution In a standard module paste in: Sub ListOutSheetNames() Application.ScreenUpdating = False Dim Nsheet As Worksheet Set Nsheet = Sheets.Add Dim WS As Worksheet Dim r As Integer r = 1 For Each WS In Worksheets If WS.Name < Nsheet.Name Then Nsheet.Range("A" & r) = WS.Name r = r + 1 End If Next WS Application.DisplayAlerts = False Application.DisplayAlerts = True Application.ScreenUpdating = True End Sub Works well for me.. HTH "arcq" wrote in message oups.com... HI! Can anybody please tell me how to put the list of Sheet Names in a list. Like for example, in a particular cell, a list would show "Sheet1", "Sheet2", & "Sheet3". Would it be possible also, that if a new Sheet will be added, ie. "Sheet4", it would just add up on that list. That list will be then used as choices to what particular sheet I would print. The situation is, I have a table on each sheet. Same format. Sometimes, new sheet/s is/are added. Within that table, I have series of codes taking data in one row, pasting it in a form, and give print. Then, goes to the next row...and so on and so forth. Please help. Thanks a lot people of great minds..... Best regards. i'll try that. thanks a lot. and as soon i can make it to work adding some other functions, i'll let you know. more power to you. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
how do i set up a list of names on a sheet frm various sheets in e | Excel Discussion (Misc queries) | |||
Putting Sheet Names in a range and renaming it? | Excel Worksheet Functions | |||
Creating list of sheet names | Excel Programming | |||
Creating list of sheet names | Excel Programming | |||
VBA to insert list of sheet names | Excel Programming |