Copy sheet name to row with VBA-CodePudding

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? :)

CodePudding user response：

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

CodePudding user response：

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