Run-Time error '1004'

[Carin]

I need to rename worksheets based on cell A1 and found this code in this forum from 2004. I'm currently using 2010. The macro works great but generates a Run-Time error at the end. Does anyone know a workaround?


'Sub Rep()
For Each sht In ThisWorkbook.Worksheets
sht.Name = sht.Range("A1")
Next sht
End Sub

 

[JoeMo]

Do all your sheets get the names you have in A1 of each sheet, including any hidden sheets? If not, one or more sheets may have characters (like "/") in A1 that are not allowed for sheet names.

 

[JLGWhiz]

Try this

Sub Rep()
     For Each sht In ThisWorkbook.Worksheets
         If sht.Range("A1") <> "" Then
             sht.Name = sht.Range("A1")
        End If
    Next sht
 End Sub

You need data in Cell A1 for the sheets you want to change the name on.

 

[Carin]

This works perfect - @JoeMoe - there were no characters, I actually had blank worksheets.
@JLGWhiz - thank you. I didn't consider having blank worksheets causing a problem.

 

[JLGWhiz]

This works perfect - @JoeMoe - there were no characters, I actually had blank worksheets.
@JLGWhiz - thank you. I didn't consider having blank worksheets causing a problem.

they wouldn't be if you were addressing one sheet to make the name change, but your code addresses each sheet in the workbook, so if any of the sheets have no data in cell A1, you get the error message.
Regards, JLG