naming a worksheet

  • Thread starter Thread starter sie
  • Start date Start date
You need to do it with VBA

with worksheets("Sheet1")
.name = .Range("A1")
End If

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)
 
You can use this with a macro

Sub test()
On Error Resume Next
ActiveSheet.Name = Range("B2").Value
End Sub
 
How do you enable a macro? How is this Code supposed to be
entered, and what category should it fall under
(Worksheet, activate, change???)
 
To learn more about macros, see David McRitchie's "Getting Started with
Macros and User Defined Functions":

http://www.mvps.org/dmcritchie/excel/getstarted.htm


Bob gave your bare-bones code. You can run it as a stand alone macro in
a regular code module (Insert/Module in the VBE):

Public Sub NameSheet()
On Error Resume Next 'in case name is invalid
With Worksheets("Sheet1")
.Name = .Range("A1").Value
End With
On Error GoTo 0
End Sub

To instead automatically change the worksheet name when the cell value
is changed, put this in the Worksheet code module (right-click the
worksheet tab and choose View Code):

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
On Error Resume Next 'in case name is invalid
If Not Intersect(Target, Range("A1")) Is Nothing Then _
Me.Name = Range("A1").Value
On Error GoTo 0
End Sub
 
Back
Top