CODEWITHSUNDEEP
vbaexcel
John B. Walugembe
John B. Walugembe

Reputation: 589

Paste worksheet names in multiple worksheets

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.

Upvotes: 2

Views: 2016

Answers (4)

Fadi
Fadi

Reputation: 3322

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.

Upvotes: 2

user4039065
user4039065

Reputation:

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.

Upvotes: 2

Ollie
Ollie

Reputation: 337

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

Upvotes: 2

R3uK
R3uK

Reputation: 14547

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

  1. workbook_to_scan's Name and
  2. 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

Upvotes: 0

Related Questions