Macro to name a sheet

[Jamie]

Hi there

I am trying to create a macro that basically copies the
text in a cell and pastes this into the worksheet tab.
Basically I want to rename the worksheet based on what is
in a particular cell.

I have tried recording a macro to do this but in the VB
code it doesn't pick up what is in the cell it stores the
actual text that was in the cell when I recorded the macro.

Help

Any help would be much appreciated

Thanks in advance

Jamie

 

[Frank Kabel]

Hi
try
sub foo()
with activesheet
.name = .range("A1").value
end with
end sub

 

[Paul B]

Jamie, here is one way

activesheet.name =[a1]


--
Paul B
Always backup your data before trying something new
Please post any response to the newsgroups so others can benefit from it
Feedback on answers is always appreciated!
Using Excel 2000 & 2003
** remove news from my email address to reply by email **

 

[mzehr]

Hi
Frank Kabel had posted this earlier. Might suit your needs


Worksheet Name Change According to Cell Value
In the worksheet's module (right-click the sheet's tab |
View Code), paste the following:

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Cells.Count > 1 Then Exit Sub
If Intersect(Target, Me.Range("A1")) Is Nothing Then
Exit Sub
On Error GoTo CleanUp
application.enableevents = false
With Target
If .Value <> "" Then
Me.Name = .value
End If
End With
CleanUp:
Application.EnableEvents = True
End Sub