I need to return the name of a worksheet in another worksheet cell?

[Rick]

This is either very easy or just can't be done.
I want the first worksheet in a workbook to display a list of the other
worksheet's name.

Within Sheet1
cell A5 should return the name of Sheet2 (as renamed on the tab)
cell A6 should return the name of Sheet3
and so forth

Any ideas would be very much appreciated.

Thanks, Rick

 

[abcd]

dim Sh as worksheet, r as range
Set R = Range("A5")
for each Sh in Sheets
R.value = Sh.name
set R = R.offset(1,0)
next

 

[abcd]

If you want my macro to works fine with many books opened even when
selecting an other book you can specify the book

replace "in Sheets" by "in ThisWorkBook.Sheets"
or specifie a choosen book