change sheet name based on cell
 

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

change sheet name based on cell
 

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

change sheet name based on cell
 

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

change sheet name based on cell
 

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

change sheet name based on cell
 

Forgot to mention that this code will work even if you have a formula (like
vlookup) in cell A1.

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


"Jacob Skaria" wrote:

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