return sheet name to cell.
 

I have several worksheets and I will constantly create more worksheets.
on one of me worksheets I want to insert the name of each worksheet in
my workbook into a table.

I would like for the info to be entered in the chart at b12 and then
continue on downwards b13, etc..

on the page that contains the table there is currently no code attached
to it.

please provide correct coding info to accomplish this task.


--
oberon.black
------------------------------------------------------------------------
oberon.black's Profile: http://www.excelforum.com/member.php...o&userid=26732
View this thread: http://www.excelforum.com/showthread...hreadid=466570

return sheet name to cell.
 

Hi Oberon,

Try:

'========================
Public Sub ListSheets()
Dim tSH As Worksheet
Dim SH As Worksheet
Dim Rng As Range
Dim i As Long
Const ListAddress As String = "B12"

Set tSH = ThisWorkbook.Sheets("Sheet1") '<<==== CHANGE

Set Rng = tSH.Range(ListAddress)

For Each SH In ThisWorkbook.Sheets
If Not SH.Name = tSH.Name Then
Rng.Offset(i).Value = SH.Name
i = i + 1
End If
Next SH

End Sub
'========================

---
Regards,
Norman



"oberon.black"
wrote in message
news:oberon.black.1v6hud_1126415119.3323@excelforu m-nospam.com...

I have several worksheets and I will constantly create more worksheets.
on one of me worksheets I want to insert the name of each worksheet in
my workbook into a table.

I would like for the info to be entered in the chart at b12 and then
continue on downwards b13, etc..

on the page that contains the table there is currently no code attached
to it.

please provide correct coding info to accomplish this task.


--
oberon.black
------------------------------------------------------------------------
oberon.black's Profile:
http://www.excelforum.com/member.php...o&userid=26732
View this thread: http://www.excelforum.com/showthread...hreadid=466570