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

macropod · 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.