Variable to rename a tab in a worksheet via VBA

Chayes · #1 08-04-2012, 06:12 AM

Hi

I'm trying to rename a worksheet tab via VBA.

My problem is that the tab has a changing name.

EG
Sheets("Sheet1").Select
Sheets("Sheet1").Name = "File 1"

"Sheet1" in this case will change in name next time I want to run the macro , so it will fail.

How can I build in a variable for the sheet selection so I can always rename it 'File 1' , whatever it used to be. It's always and only the first sheet I'm applying this to , by the way.

Grateful for any help.

Colin Legg · #2 08-04-2012, 08:14 AM

If the sheet will always be in the same position then you can use its Index instead of its Name.

eg

Code:

Sheets(1).Select

Even better, if your code is in the same workbook, you can use its codename.

eg

Code:

Sheet1.Select

To see the difference between a sheet's Name and Codename, have a look at the attachment on this thread:
https://www.msofficeforums.com/excel...html#post36290

macropod · #3 08-05-2012, 05:36 PM

I'd prefer to work with sheet names. That way, if someone re-orders the worksheets, the code won't try to process the wrong one. To ensure the code always processes a sheet named "File 1", even if that sheet starts out named "Sheet1", you could use code like:

Code:

Sub Demo()
Dim xlWs As Worksheet, bFound1 As Boolean, bFound2 As Boolean
With ThisWorkbook
  bFound1 = False
  For Each xlWs In .Worksheets
    With xlWs
      If .Name = "File 1" Then
        bFound1 = True
        MsgBox .Range("A1").Value
        Exit For
      ElseIf .Name = "Sheet1" Then
        bFound2 = True
      End If
    End With
  Next
  If bFound1 = False And bFound2 = True Then
    .Worksheets("Sheet1").Name = "File 1"
    MsgBox .Worksheets("File 1").Range("A1").Value
  End If
End With
End Sub

Note also that nothing needs to be selected.

Colin Legg · #4 08-05-2012, 11:29 PM

Quote:

Originally Posted by macropod

I'd prefer to work with sheet names. That way, if someone re-orders the worksheets, the code won't try to process the wrong one.

The reason I suggested using the worksheet's index is the OP said:

Quote:

It's always and only the first sheet I'm applying this to , by the way.

However, the fact that the user can change the both the sheet's order and name (if workbook structure is left unprotected) is why I suggested using the sheet's codename instead, because that cannot be changed by the user unless the user goes into the VBA IDE. This would be my preference.

macropod · #5 08-06-2012, 08:42 PM

Hi Colin,

I don't have any issues with that - as far as it does. IMHO, though, it's not good to pre-suppose the required sheet will always be the first sheet and, given that the desired sheet might have one of two names, positively identiying the sheet via the name is the better approach.

Colin Legg · #6 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

Similar Threads
Thread	Thread Starter	Forum	Replies	Last Post
How to Rename a Quick Part	sleake	Word	14	10-14-2013 09:21 AM
Run-time error '91': Object variable or With block variable not set	tinfanide	Excel Programming	2	06-10-2012 10:17 AM
Rename Files	gsrikanth	Excel Programming	3	05-14-2012 03:03 AM
"Auto-populating" data-worksheet to worksheet.	meggenm	Excel	4	02-04-2012 02:04 AM
Rename File	cksm4	Word VBA	2	02-25-2011 09:29 AM