CODEWITHSUNDEEP
vbaexcel
Ongok Ongoksepen
Ongok Ongoksepen

Reputation: 305

Rename Excel Sheet with VBA Macro

I want to ask about rename the excel sheet, i want to rename the sheet with new name : older name + _v1.

So if my current sheet name is test, then I want the new name test_v1.

I only know the standard vba for rename excel sheet which is renaming excel sheet by the sheet content.

Sub Test()

Dim WS As Worksheet

For Each WS In Sheets
   WS.Name = WS.Range("A5")
Next WS
End Sub

Upvotes: 19

Views: 240214

Answers (3)

Realitybites
Realitybites

Reputation: 443

The "no frills" options are as follows:

ActiveSheet.Name = "New Name"

and 

Sheets("Sheet2").Name = "New Name"

You can also check out recording macros and seeing what code it gives you, it's a great way to start learning some of the more vanilla functions.

Upvotes: 26

brettdj
brettdj

Reputation: 55682

Suggest you add handling to test if any of the sheets to be renamed already exist:

Sub Test()

Dim ws As Worksheet
Dim ws1 As Worksheet
Dim strErr As String

On Error Resume Next
For Each ws In ActiveWorkbook.Sheets
Set ws1 = Sheets(ws.Name & "_v1")
    If ws1 Is Nothing Then
        ws.Name = ws.Name & "_v1"
    Else
         strErr = strErr & ws.Name & "_v1" & vbNewLine
    End If
Set ws1 = Nothing
Next
On Error GoTo 0

If Len(strErr) > 0 Then MsgBox strErr, vbOKOnly, "these sheets already existed"

End Sub

Upvotes: 5

Tim Williams
Tim Williams

Reputation: 166306

This should do it:

WS.Name = WS.Name & "_v1"

Upvotes: 20

Related Questions