Rename Worksheets Excel 2007

[tf37]

This macro works for the most part, but what happens is it doesn’t completely rename the entire sheet.
If the sheet name is “Sheet1” and you run the macro, it renames it to “Sheet71 1”
What am I missing here to get it to just rename the Sheet1 to Sheet71 without leaving the 1 after the rename?
If steps to the next sheet, like Sheet2 and renames it Sheet72 2 and so on.

Sub RenameSheets()
Dim ws As Worksheet

For Each ws In Worksheets
If ws.Name Like "Sheet#*" Then
ws.Name = Replace(ws.Name, "Sheet", "Sheet71 ", 1, 1)
End If
Next ws
End Sub

 

[alansidman]

What do you want Sheet2 to be? 72?

 

[Fluff]

Try

Sub RenameSheets()
Dim ws As Worksheet

For Each ws In Worksheets
If ws.Name Like "Sheet#*" Then
ws.Name = Replace(ws.Name, "Sheet", "Sheet7", 1)
End If
Next ws
End Sub

 

[alansidman]

If you want them numbered sequentially, ie. 71, 72, etc then

Option Explicit


Sub RenameSheets()
    Dim ws As Worksheet
    Dim i As Long
    i = 1
    For Each ws In Worksheets
        If ws.Name Like "Sheet#*" Then
            ws.Name = Replace(ws.Name, "Sheet" & i, "Sheet7" & i)
        End If
        i = i + 1
    Next ws
End Sub

 

[tf37]

Wish to rename a set of worksheets, if the name is Sheet2, and so on, to Sheet71 then Sheet72, etc...

 

[tf37]

Option Explicit


Sub RenameSheets()
Dim ws As Worksheet
Dim i As Long
i = 1
For Each ws In Worksheets
If ws.Name Like "Sheet#*" Then
ws.Name = Replace(ws.Name, "Sheet" & i, "Sheet7" & i)
End If
i = i + 1
Next ws
End Sub

This does what I started off with. It renames the worksheet from "Sheet1" to "Sheet71 1"
I would like it to drop the end number "1" and just have the sheet name "Sheet71"
Thanks for look at this for me

 

[alansidman]

Terry
I cannot replicate your issue. I run the code I provided and the Sheet names are changed to Sheet71, Sheet72, Sheet73, etc.

Please use code tags when posting code. It is easier to read and understand.

 

[tf37]

Ok, I'll give it another try. Thank you all for the help.
If I continue to run into problems I'll post a follow up on it.
And I guess using a code tag is like those above, where it appears in a box.
I've never used it that I know of, and not really aware of it until now, thanks again for the heads up too.

 

[Fluff]

Did you try my suggestion in post#3?

 

[tf37]

Did you try my suggestion in post#3?

Yes, sure did. What I end up with is it renames Sheets1 - 9 as Sheet71 - 79 just fine, but when it gets to Sheet10, it names it Sheet710 and not Sheet80.
Another post asked for me to post with code tag to make it easier to read, but I guess I don't use the Forum enough...I'm not how to do that.
My post options don't allow for me to paste a copy of a worksheet either. I think it use to, but unfortunately, I don't see any method to add attachments of paste pictures.
I check my options, and if I use Advanced reply I see I can paste pictures then.
Anyway, I know this use to work just fine, and would rename all my sheets, not sure what has changed in my system, or Office 2007 that has changed it.
I'll continue to play with it, or finally just do it manually.
Thanks again