Microsoft Office Forums - View Single Post - Variable to rename a tab in a worksheet via VBA

Colin Legg · 08-07-2012, 02:03 AM

Hi Paul,

I'll leave it to the OP to validate his/her assumption that the target sheet will always be the first sheet.

I realised I forgot to post some sample Codename code for the OP, which is the approach I favour.

Code:

Sub demo()
 
    Const strNEW_NAME As String = "File 1"
 
    'a workbook cannot have sheets with duplicate names
    If Not SheetExists(ThisWorkbook, strNEW_NAME) Then
        'sheet1 is the codename of the sheet, determined at design time
        Sheet1.Name = strNEW_NAME
    End If
 
End Sub
 
'a generic function to check if a sheet exists
Private Function SheetExists(ByVal wkbTarget As Workbook, ByRef strName As String)
    On Error Resume Next
    SheetExists = Not wkbTarget.Sheets(strName) Is Nothing
End Function