Copy sheet name to row with VBA

Question

I need to copy the worksheets name into Row 1 of another worksheet of the same file. Here is what I have/need:

• File has different worksheets, but I need only the title from the sheet 3 on
• The worksheet where I want to copy the names is sheet 2
• the names need to be copy on row 1, from cell B1
• I'll run this macro periodically, so I'd need new sheet names to be added every time while keeping there the one already copied.

Can you help me? :)

Answer 1

List Worksheet Names in a Row

Sub ListNames()
    
    Dim wb As Workbook: Set wb = ThisWorkbook ' workbook containing this code
    Dim dCell As Range: Set dCell = wb.Worksheets(2).Range("B1")
    
    Dim n As Long
    For n = 3 To wb.Worksheets.Count
        dCell.Value = wb.Worksheets(n).Name
        Set dCell = dCell.Offset(, 1)
    Next n
    
End Sub

Answer 2

From what you write it's really hard to tell what you actually need.

The following VBA code writes the name of the 3rd worksheet to the B1 cell of sheet 2. If you don't have 3 sheet's nothing happens:

Sub WriteTheNameOfWorksheet3IntoWorksheet2CellB1()
   If ActiveWorkbook.Worksheets.Count >= 3 Then
      ActiveWorkbook.Worksheets(2).Range("B1") = ActiveWorkbook.Worksheets(3).Name
   End If
End Sub