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
CodePudding user response:
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
CodePudding user response:
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