Paste worksheet names in multiple worksheets

Question

I have a workbook with over 50 worksheets. I would like to copy the name of each worksheet into a cell of that particular workbook. I can do it for one sheet at a time using a macro with the following VBA code:

Range("B1") = ActiveSheet.Name

But when I try to apply the macro to several worksheets at a time, it fails. I would like it to get the names of the first 30 worksheets only.

Can we see your full code that you have attempted? It is difficult to find fault in one line. — Scott Craner, Dec 15 '15 at 15:59
What are the names of your 2 workbooks? And what is the sheet name that you want to paste the names in? — R3uK, Dec 15 '15 at 16:00
In addition to the correct VBA answers, the problem could also be solved by selecting all the tabs for the worksheets of interest and applying the following formula in cell B1 of the current worksheet: `=RIGHT(CELL("filename",A1),LEN(CELL("filename",A1))-SEARCH("]",CELL("filename",A1)))` Courtesy phantom1975 at https://www.mrexcel.com/forum/excel-questions/33252-formula-get-worksheet-name-solved.html. — John B. Walugembe, Oct 13 '17 at 06:12

score 2 · Accepted Answer · edited May 23 '17 at 10:27

Avoid relying on the ActiveSheet property to identify the worksheet you want to process. The With ... End With statement can readily provide the worksheet and help retrieve the Worksheet .Name property.

Sub name_Worksheets()
    Dim w As Long

    For w = 1 To 30
        With Worksheets(w)
            .Cells(1, 2) = .Name
        End With
    Next w
End Sub

_{See How to avoid using Select in Excel VBA macros for more methods on getting away from relying on select and activate to accomplish your goals.}

score 2 · Answer 2 · answered Dec 15 '15 at 16:09

My Understanding is you want to 1) Go through first 30 sheets of your workbook and 2) Paste the sheet name into cell B1.

Sub PasteSheetNameInB1()

    For i = 1 To 30 '(1 to 30 because you said " I would like it to get the names of the first 30 worksheets only.")
        ActiveWorkbook.Sheets(i).Select 'Iterates through first 30 sheets
        Range("B1") = ActiveSheet.Name 'Pastes Sheet name into B1
    Next i


End Sub

score 2 · Answer 3 · answered Dec 15 '15 at 18:20

You can use this code:

For i = 1 To 30
 Sheets(i).Range("B1").Formula = "=MID(CELL(""filename"",A1),FIND(""]"",CELL(""filename"",A1))+1,255)"
Next

Now if you change the name of any worksheet, You don't need to run the macro again, the formula in Rnage("B1") will display the new name.

score 0 · Answer 4 · answered Dec 15 '15 at 16:05

So with this code, that you'll paste in the destination workbook,
you'll just need to change :

workbook_to_scan's Name and
Sheet's name in which to paste the names

to fit your needs!

Sub test_johnB()
Dim wB1 As Workbook, _
    wB2 As Workbook, _
    wSDest As Worksheet, _
    wS As Worksheet, _
    i As Integer

Set wB1 = ThisWorkbook
Set wB2 = Workbooks("workbook_to_scan's Name")
Set wSDest = wB1.Sheets("Sheet's name in which to paste the names")

i = 0
For Each wS In wB2.Sheets
   wSDest.Range("B1").Offset(i, 0) = wS.Name
Next wS

End Sub

Paste worksheet names in multiple worksheets

4 Answers4