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