copy all sheet tab names of a file in a cell

[MA]

hi,

I want to copy the names of all existing sheet names of a file as a list in
any of the sheets of the file.

Your expert advice is highly appreciated.

 

[Don Guillett]

sub toc()
for i=1 to sheets.count
cells(i,1).value=sheets(i).name
next i
end sub

 

[MA]

Hi Don,

Thanks for your quick response. This is working perfectly fine, if can you
help in the scenario the list is required to be copied on to a specific cell
reference in a specific sheet of the file.

thanks for your help.

MA

 

[Gord Dibben]

Private Sub ListSheets()
'list of sheet names starting at H5
Dim rng As Range
Dim I As Integer
Set rng = Sheets("Sheet3").Range("H5") 'adjust sheetname and range
For Each Sheet In ActiveWorkbook.Sheets
rng.Offset(I, 0).Value = Sheet.Name
I = I + 1
Next Sheet
End Sub


Gord Dibben MS Excel MVP

 

[Don Guillett]

Pretty simple. Just add the destination sheet and adjust the i starting row
and ,1 starting column
Below starts at cell C7
Sub toc()
For i = 1 To Sheets.Count
'.cells(i,1).value=sheets(i).name
Sheets("sheet10").Cells(i + 6, 3).Value = Sheets(i).Name
Next i
End Sub