• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Renaming worksheets

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?


Code:
Sub RenamWs()


Dim WS As Worksheet

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


End Sub
Thanks
 

Marc L

Excel Ninja
Hi !​
Nothing wrong except the logic so check each range and each worksheet name (max 31 chars) …​
 
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
 

AlanSidman

Active Member
Here is another solution:
Code:
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
 
Top