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)

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

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
.

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
.

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
.


.

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
.


.