Naming worksheet based on value in cell...ERROR message

[vik2slick]

Hi,

I am trying to rename my worksheets (540 of them) based on the value in cell A200 (which is a formula pulling together 3 cells, so =A1&" "&B1&" "&A3 ). I've checked all the sheets to make sure there is a value in cell A200. However, when I run the below macro, I get a Run-time error.

VBA used
Sub test()
Dim ws As Worksheet
For Each ws In ActiveWorkbook.Worksheets
ws.Activate
ActiveSheet.Name = Range("A200").Value
Next ws
End Sub


Error received when VBA used
Run-time error '1004':
Application-defined or object-defined error


Can anyone help/advise?

 

[Norie]

Does this work any better?

Sub test()
Dim ws As Worksheet

    For Each ws In ActiveWorkbook.Worksheets
        ws.Name = ws.Range("A200").Value
    Next ws

End Sub

 

[vik2slick]

Worked for the first 22 sheets but the error popped up again. I checked the 23rd sheet to see if there was any mistake but none that i can see. There is a value there.

 

[My Aswer Is This]

Try this:

Sub My_Sheet_Names()
Application.ScreenUpdating = False
'Modified  9/26/2018  7:10:38 PM  EDT
Dim i As Long
On Error GoTo M
    For i = 1 To Sheets.Count
        Sheets(i).Name = Sheets(i).Range("A200").Value
    Next
Application.ScreenUpdating = True
Exit Sub
M:
MsgBox "That sheet name may have already been used or is a improper sheet name"
Application.ScreenUpdating = True
End Sub

 

[vik2slick]

Came back with MsgBox "That sheet name may have already been used or is a improper sheet name". The characters in that cell are a few more than the limit for the tab name. Thank you!!

 

[My Aswer Is This]

So you need to fix that. The limit is unknow to me.

And there are some characters you cannot use like /

 

[vik2slick]

Thank you; appreciate the help!

 

[My Aswer Is This]

Thank you; appreciate the help!

Glad I was able to help you.