Rename multiple worksheets in a workbook with Date value in cell B3

Question

I'm trying to rename multiple worksheets of a workbook from their default names of "Sheet1 (1), Sheet1 (2), Sheet1 (3),..." to the date value that is in the cell "B3" of each particular worksheet. I want to end up with worksheet names of the dates that are in their individual "B3" cells. There are no duplicate dates in any of the worksheets. The format of the date in cell "B3" of each worksheet is in the format of 14-Mar-2001 as an example. Here is the macro I'm trying to use but it gives me a

run-time error '1004'

Sub RenameTabs()
    For x = 1 To Sheets.Count
        If Worksheets(x).Range("B3").Value <> "" Then
            Sheets(x).Name = Worksheets(x).Range("B3").Value
        End If
    Next
End Sub

Any help I can get would be greatly appreciated!

Answer 1

Note that you mix Sheets and Worksheets. This is not the same!

While Sheets can contain worksheet and chart objects, Worksheets only contains worksheets. Therefore there might exist 8 sheets (5 worksheets and 3 charts). So change all Sheets to Worksheets.

Also you need to make sure the worksheet name is not more than 31 characters long because this is a limit of Excel.

Option Explicit 

Public Sub RenameTabs()
    Dim x As Long
    For x = 1 To Worksheets.Count
        If Worksheets(x).Range("B3").Value <> "" Then
            Worksheets(x).Name = Left$(Worksheets(x).Range("B3").Value, 31)
        End If
    Next
End Sub

If your cell B3 is a real date, you should either use Format$() or change it to Worksheets(x).Range("B3").Text otherwise your .Value will return the numeric value of the date instead of the formatted date.

Answer 2

Try:

Option Explicit

Sub test()

    Dim ws As Worksheet

    With ThisWorkbook

        'Loop worksheets
        For Each ws In .Worksheets

            With ws
                'Check ws b3 value
                If .Range("B3").Value <> "" Then
                    'set ws name
                    ws.Name = .Range("B3").Value
                End If

            End With

        Next ws

    End With

End Sub

NOTE

Have in mind that worksheet name should be up to 31 characters