Worksheet names into Cells

[helpexcel]

Does anyone have a code that would put the names of all worksheets to the right of sheet3, in the cells of row 1 after the last column in the last worksheet. Thanks!

 

[mumps]

Try:

Sub GetSheetNames()
    Dim lColumn As Long
    Dim ws As Worksheet
    For Each ws In Sheets
        lColumn = Sheets(Sheets.Count).Cells(1, Columns.Count).End(xlToLeft).Column
        Sheets(Sheets.Count).Cells(1, lColumn + 1) = ws.Name
    Next ws
End Sub

 

[helpexcel]

That works great, but it pulls all the worksheet names. Is there a way to only pull a certain range of worksheets. For example if you had 10 worksheets, you would pull the names from worksheets 4-8?

 

[mumps]

Try:

Sub GetSheetNames()
    Dim lColumn As Long
    Dim x As Long
    For x = 4 To 8
        lColumn = Sheets(Sheets.Count).Cells(1, Columns.Count).End(xlToLeft).Column
        Sheets(Sheets.Count).Cells(1, lColumn + 1) = Sheets(x).Name
    Next x
End Sub