Quickie macro to return worksheet names into a list

[mach3]

could someone please offer up a quick macro that returns sheet names (i.e., tab names) into a pre-defined column (let's say column B, rows 100 to 120 or so). 1 wrinkle, please return them in worksheet order (left to right). thanks.


mach3

[RET79]

Try this

Sub tabnames()


numberOfSheets = Worksheets.Count

For i = 1 To numberOfSheets Step 1
Cells(99+ i, 1)= Worksheets(i).Name
Next i

End Sub

Hope this helps,

RET79

[RET79]

OR even more slick, use offset:


Sub tabnames()

numberOfSheets = Worksheets.Count

For i = 1 To numberOfSheets Step 1
Cells(100, 1).Offset(i - 1, 0) = Worksheets(i).Name
Next i

End Sub

[Colo]

Please also try this code.

Dim sh As Worksheet, i As Integer
With Range("B100")
Range(.Resize, .End(xlDown)).ClearContents 'Clear former data
For Each sh In Worksheets 'Loop
.Offset(i).Value = sh.Name
i = i + 1
Next
End With
End Sub