Variable to rename a tab in a worksheet via VBA

macropod · #1 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 · #2 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.

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