Try the below...

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
If Range("A1") < Sh.Name And Trim(Range("A1")) < "" Then _
Sh.Name = Trim(Range("A1"))
End Sub

If this post helps click Yes
---------------
Jacob Skaria


"soph" wrote:

perfect thanks! at first I couldn't get it to work but then realised that A1
can't be the result of a lookup formula? when I changed this to a simple
values only it was dead on cheers

"Jacob Skaria" wrote:

Set the security level to low/medium in (Tools|Macro|Security). From workbook
press Alt+F11 to launch VBE (Visual Basic Editor). From the left treeview
search for the workbook name and click on + to expand it. Within that you
should see the following

VBAProject(Your_Filename)
Microsoft Excel Objects
Sheet1(Sheet1)
Sheet2(Sheet2)
Sheet3(Sheet3)
This Workbook

Double click 'This WorkBook' and paste the below code to the right code pane.

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
On Error Resume Next
If Target.Count = 1 And Target.Address = "$A$1" Then
If Trim(Target.Text) < "" Then Sh.Name = Trim(Range("A1"))
End If
End Sub


PS: Sheetname Max length 31 and exxception characters are [ ] * ? / \

If this post helps click Yes
---------------
Jacob Skaria


"soph" wrote:

Hi

I have searched through here and found some similar questions but none of
the answers seem to work for me. I have a work book with approx 20 sheets
each for a different team, and each sheet has the team name in A1 that I need
reflected in the sheet name. The cell information may change as the team
name changes. Any help would be greatly appreciated!

Cheers
Soph