change sheet name based on cell

  • Thread starter Thread starter soph
  • Start date Start date
S

soph

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
 
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
 
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 said:
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 said:
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
Click to expand...
Click to expand...
 
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 said:
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 said:
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 said:
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
Click to expand...
Click to expand...
Click to expand...
 
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 said:
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 said:
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 said:
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


:

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
Click to expand...
Click to expand...
Click to expand...
 
Back
Top