Sheet Name Refering to Cell Value

  • Thread starter Thread starter Greg Rivet
  • Start date Start date
G

Greg Rivet

I want to name a sheet to the cell value in A2. Cell A2 has an Alt-enter in
the cell. I want the sheet name to be the cell value of the first line only.
Can anybody help. TIA

Greg
 
One way:

Option Explicit
Sub testme01()
Dim myStr As String
Dim vbLFPos As Long

With ActiveSheet
myStr = .Range("a2").Value
vbLFPos = InStr(1, myStr, vblf)
If vbLFPos > 0 Then
myStr = Left(myStr, vbLFPos - 1)
On Error Resume Next
.Name = myStr
If Err.Number <> 0 Then
MsgBox "couldn't rename " & .Name & " to: " & myStr
Err.Clear
End If
On Error GoTo 0
End If
End With
End Sub
 
Back
Top