Spreadsheet: 195871d1353698294-possible-to-change-sheets-names-automatically-based-on-cell-values-in-other-sheets-sheetname-from-cell-automatically
Sheet: Sheet4

http://www.pcreview.co.uk/forums/change-sheet-name-based-cell-t3891356.html
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
 | 20th Sep 2009
[NAME] | Set the security level to low/medium in (Tools|Macro|Security). From workbook
Guest | press Alt+F11 to launch VBE (Visual Basic Editor). From the left treeview
Posts: n/a | 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 [NAME]
 | 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 [NAME] length 31 and exxception characters are [ ] * ? / \
 | If this post helps click Yes
 | ---------------
 | [NAME]
 | "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
 | 20th Sep 2009
soph | perfect thanks! at first I couldn't get it to work but then realised that A1
Guest | can't be the result of a lookup formula? when I changed this to a simple
Posts: n/a | values only it was dead on cheers
 | "[NAME]" 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 [NAME] length 31 and exxception characters are [ ] * ? / \
 | >
 | > If this post helps click Yes
 | > ---------------
 | > [NAME]
 | >
 | >
 | > "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
 | 20th Sep 2009
[NAME] | Try the below...
Guest
Posts: n/a | 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
 | ---------------
 | [NAME]
 | "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
 | >
 | > "[NAME]" 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 [NAME] length 31 and exxception characters are [ ] * ? / \
 | > >
 | > > If this post helps click Yes
 | > > ---------------
 | > > [NAME]
 | > >
 | > >
 | > > "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
 | 20th Sep 2009
[NAME] | Forgot to mention that this code will work even if you have a formula (like
Guest | [NAME]) in cell A1.
Posts: n/a
 | If this post helps click Yes
 | ---------------
 | [NAME]
 | "[NAME]" 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
 | > ---------------
 | > [NAME]
 | >
 | >
 | > "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
 | > >
 | > > "[NAME]" 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 [NAME] length 31 and exxception characters are [ ] * ? / \
 | > > >
 | > > > If this post helps click Yes
 | > > > ---------------
 | > > > [NAME]
 | > > >
 | > > >
 | > > > "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