Home |
Search |
Today's Posts |
#1
|
|||
|
|||
Extracting worksheet names....
Hello,
I use the following formula to display worksheet names in a cell: =MID(CELL("filename",A1),FIND("]",CELL("filename",A1)) +1,255) Is there a way to list the names of all the worksheets in the entire workbook on one sheet? (thanks) |
#2
|
|||
|
|||
run the following code:
Sub test() Set rng = Worksheets("Sheet1").Range("A1") i = 1 For Each sht In Worksheets rng(i, 1) = sht.Name i = i + 1 Next End Sub place it in any module to run it. - Mangesh -- Message posted via http://www.officekb.com |
#3
|
|||
|
|||
thanks, but is there a way to automatically update the
list if i was to insert another worksheet?? -----Original Message----- run the following code: Sub test() Set rng = Worksheets("Sheet1").Range("A1") i = 1 For Each sht In Worksheets rng(i, 1) = sht.Name i = i + 1 Next End Sub place it in any module to run it. - Mangesh -- Message posted via http://www.officekb.com . |
#4
|
|||
|
|||
You could place Mangesh's example in "ThisWorkbook" code page
Private Sub Workbook_NewSheet(ByVal Sh As Object) Set rng = Worksheets("Sheet1").Range("A1") i = 1 For Each sht In Worksheets rng(i, 1) = sht.Name i = i + 1 Next End Sub "johnT" wrote: thanks, but is there a way to automatically update the list if i was to insert another worksheet?? -----Original Message----- run the following code: Sub test() Set rng = Worksheets("Sheet1").Range("A1") i = 1 For Each sht In Worksheets rng(i, 1) = sht.Name i = i + 1 Next End Sub place it in any module to run it. - Mangesh -- Message posted via http://www.officekb.com . |
#5
|
|||
|
|||
i can't seem to get this working???
-----Original Message----- You could place Mangesh's example in "ThisWorkbook" code page Private Sub Workbook_NewSheet(ByVal Sh As Object) Set rng = Worksheets("Sheet1").Range("A1") i = 1 For Each sht In Worksheets rng(i, 1) = sht.Name i = i + 1 Next End Sub "johnT" wrote: thanks, but is there a way to automatically update the list if i was to insert another worksheet?? -----Original Message----- run the following code: Sub test() Set rng = Worksheets("Sheet1").Range("A1") i = 1 For Each sht In Worksheets rng(i, 1) = sht.Name i = i + 1 Next End Sub place it in any module to run it. - Mangesh -- Message posted via http://www.officekb.com . . |
#6
|
|||
|
|||
In the visual basic editor right click on the "ThisWorbook" icon and select
view code. Paste the example there. It won't work in a regular code module "johnT" wrote: i can't seem to get this working??? -----Original Message----- You could place Mangesh's example in "ThisWorkbook" code page Private Sub Workbook_NewSheet(ByVal Sh As Object) Set rng = Worksheets("Sheet1").Range("A1") i = 1 For Each sht In Worksheets rng(i, 1) = sht.Name i = i + 1 Next End Sub "johnT" wrote: thanks, but is there a way to automatically update the list if i was to insert another worksheet?? -----Original Message----- run the following code: Sub test() Set rng = Worksheets("Sheet1").Range("A1") i = 1 For Each sht In Worksheets rng(i, 1) = sht.Name i = i + 1 Next End Sub place it in any module to run it. - Mangesh -- Message posted via http://www.officekb.com . . |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Autofill Worksheet names | Setting up and Configuration of Excel | |||
How do I print a list of worksheet tab names in a workbook | Excel Worksheet Functions | |||
drop down menu containing worksheet names | Excel Worksheet Functions | |||
How to link Excel worksheet tab names to dates in each worksheet? | Excel Worksheet Functions | |||
Dynamic Worksheet Names | Excel Worksheet Functions |