Getting error

Question

Getting "run-time error '1004': Mathod 'name of object'_Worksheet' failed "

Sub Change_name()

Dim sh As Worksheet

For Each sh In ThisWorkbook.Worksheets

sh.name = Left(sh.Range("A5").Value, 3)

Next sh

End Sub

Answer 1

Hi Narayan,

try it this way:

Sub Change_name() 

Dim i As Integer 

With ThisWorkbook 

      For i = 1 To .Worksheets.Count 

            .Worksheets(i).Name = Left(Sheets(i).Range("A5"), 3) 

      Next 

End With 

End Sub

Claus

Answer 2

its showing

Run-time error '424' Object required

Answer 3

When the error occurs, activate the Immediate window (Ctrl+G).

Type or copy/paste the following:

? Left(sh.Range("A1").Value, 3)

and press Enter. What is the result?

Answer 4

debug is getting on sh.name = Left(sh.Range("A1").Value, 3)

Perhaps one of the values would result in a name that is not allowed, or in a duplicate name.

• A worksheet name cannot contain any of the following characters: / \ ? * : [ ]
• A worksheet name cannot begin or end with an apostrophe '
• A worksheet name must be unique within the workbook

I have checked the above-mentioned criteria, and there is no issue with it

Answer 5

Perhaps one of the values would result in a name that is not allowed, or in a duplicate name.

• A worksheet name cannot contain any of the following characters: / \ ? * : [ ]
• A worksheet name cannot begin or end with an apostrophe '
• A worksheet name must be unique within the workbook