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
> >> >.
> >> >
> >>
> >.
> >
>