Skip to: Site menu | Main content

Login

Name: 
Password:
Remember me?
Register

Renaming worksheets sequentially

written by Mark Rowlinson - Last updated Sep 2005

The following procedure will safely name the given worksheet with the given name. If the name already exists then it will add a number until it finds one that doesn't exist as Excel does with copying sheets.

NB: It makes use of the SheetExists function found here.

 
Sub RenameSheet(ws As Worksheet,NewName As String) 
     'exits if the newname is the same as the old name
    If NewName=ws.name Then exit Sub 
     'declares
    Dim i As Integer 
    Dim TestName As String 
     'set the testname to the newname
    TestName=NewName 
     'start i at 1
    i=1 
    While SheetExists(ws.Parent,TestName) 
         'if the sheetname exists increment counter by 1
        i=i+1 
         'create a name such as "MyName(2)" as Excel would
        TestName=NewName & "(" & i & ")" 
    Wend 
     'set the new sheet name
    ws.name=TestName 
End Sub