Renaming worksheets

[ferocious12]

Hi Guys,

I am using the following macro to rename worksheets in the file (this is part of number of macros I am using). It does the job and rename all the worksheets but code stops and goes into debug mode. What may be wrong?

Sub RenamWs()


Dim WS As Worksheet

For Each WS In Sheets
   WS.Name = WS.Range("A7")
Next WS


End Sub

Thanks

 

[Marc L]

Hi !​

​

Nothing wrong except the logic so check each range and each worksheet name (max 31 chars) …​

​

 

[ferocious12]

Thanks. I believe one of the worksheet is blank and that is the reason it stops. Can I add a condition and say

if ws.name = "Data*" then
For Each WS In Sheets
WS.Name = WS.Range("A7")
end if
Next WS

But getting error

 

[Marc L]

​

Unclear so crystal clear elaborate …​

​

 

[ferocious12]

I added Resume next on error and problem is solved. Thanks

 

[AlanSidman]

Here is another solution:

Option Explicit

Sub Rename()
    Dim WS As Worksheet
    For Each WS In Sheets
        If InStr(WS.Name, "Data") > 0 Then
            WS.Name = WS.Range("A7")
        End If
    Next WS
End Sub

 

[ferocious12]

thanks