Excel VBA: Change each sheet name to cell A1 value in each sheet

Question

I have a code that is able to rename the active sheet (in this case, sheet 1) to the value in the A1 cell of the active sheet. I'd like to write a function to be able to do this for every sheet in the Excel workbook. Where sheet 1 name will be the value of cell A1 on sheet 1, sheet 2 name will be the value of cell A1 on sheet 2, and so on.

Is it as simple as counting the number of sheets in the workbook and adding this to a range Worksheets(1:TotalCount)?

Sub RenameSheet()

Worksheets(1).Select
Range("A1").Select
ActiveSheet.Name = ActiveCell.Value

End Sub

@BigBen Would it be something like a For Each Worksheet in ActiveWorkbook loop? I am new to VBA — Mike

DS_London DS_London · Accepted Answer · 2020-10-01T16:19:22

As BigBen says, you need a loop to through each sheet. Simplest way is to use the collection of Worksheet objects. Best to check the cell "A1" actually has something in it, otherwise you will get an error. Try this:

Option Explicit
Sub RenameSheets()
    Dim ws As Worksheet
    Dim strName As String

    For Each ws In ActiveWorkbook.Worksheets
         strName = ws.Range("A1").Value
         If Len(strName) > 0 Then
             ws.Name = strName
         End If
    Next ws
End Sub

You should probably also put in checks that the name hasn't already been used. Excel wont like you having two sheets with the same name. Top Excel Fact: you can't call a worksheet "History" ...

Excel VBA: Change each sheet name to cell A1 value in each sheet

1 Answers