Worksheet Names to a row

user_18733811 0
Viewed 38

Complete beginner for VBA so simpler the better. I want to collect all sheet names in excel and insert them to the first row. I'm able to collect the names with macro that I found (bellow) but I don't know how to convert the values to be in the first row only and not in the first column?

Sub TestNames()
    Dim Ws As Worksheet
Dim LR As Long

For Each Ws In ActiveWorkbook.Worksheets
LR = Worksheets("Worksheet Names").Cells(Rows.Count, 1).End(xlUp).Row + 1
'This LR varaible to find the last used row
Cells(LR, 1).Select
ActiveCell.Value = Ws.Name
Next Ws
    
End Sub

https://www.wallstreetmojo.com/vba-name-worksheet/#:~:text=In%20VBA%2C%20to%20name%20a,its%20name%20using%20Worksheet%20object.

vba excel
2 Answers

Worksheet Names to First Row

Option Explicit

Sub TestNames()
    
    Dim dws As Worksheet: Set dws = ThisWorkbook.Worksheets("Worksheet Names")

    Dim sws As Worksheet
    Dim c As Long
    
    For Each sws In ThisWorkbook.Worksheets
        c = c + 1
        dws.Cells(1, c).Value = sws.Name
    Next sws
    
End Sub
user_98140690

Very close, just check for last column, and re-arrange Cells(LR, 1).Select like in example below. Keep in mind, finding last row and last column is not very straight forward task, there are different methods, - investigate them and apply the one which fits the best.

Sub TestNames()

Dim Ws As Worksheet
Dim LastColumn As Long

For Each Ws In ActiveWorkbook.Worksheets
'LR = Worksheets("Worksheet Names").Cells(Rows.Count, 1).End(xlUp).Row + 1
LastColumn = Worksheets("Worksheet Names").Cells(1, Worksheets("Worksheet Names").Columns.Count).End(xlToLeft).Column + 1


Worksheets("Worksheet Names").Cells(1, LastColumn).Value = Ws.Name

Next Ws
    
End Sub
user_88058420
Related