Changing name of sheet using VBA

[seh27]

I have a workbook with multiple sheets. Each sheet has a title (just words in a cell). Is there VBA formula that will have the sheet take the name of the title and also chnage when the title name changes? Thanks

 

[VoG]

Try like this in the ThisWorkbook module - change A1 to suit

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
If Target.Address(False, False) = "A1" Then Sh.Name = Target.Value
End Sub

 

[kevatarvind]

use below code in ThisWorkbook module - change A1 to which Cell your Title have

Private Sub Workbook_SheetSelectionChange(ByVal Sh As Object, ByVal Target As Range)
ActiveSheet.Name = Range("A1").Value
End Sub

 

[seh27]

I have to wait until tomorrow to try this but seeing as two responses were the same answer these should work. Thanks! To clarify though, what is the ThisWorkbook Module?

 

[VoG]

To clarify though, what is the ThisWorkbook Module?

Press ALT + F11 to open the Visual Basic Editor, in the Project window double click ThisWorkbook. Paste in the code.

 

[seh27]

I understand now. I pasted the code, how do I run it now? I apologize for these questions that seem some what obvious but I know how to use VBA only at a minimum.

 

[Jonmo1]

It runs automatically whenever you change the value in A1 on one of your sheets.

 

[seh27]

For some reason it is not working

 

[Jonmo1]

Well, since you haven't really described any details about where the "title" is stored, we've just been guessing that it's in the cell A1.
And also, we don't know "how" that cell changes. Is that cell a formula or do you change it by hand?

So please give some more details of exactly what you want to accomplish

 

[seh27]

Cell A1 is correct. The title for each sheet is in cell A1 but part of the title is coming from a different. For example the formula of cell A1 of one sheet looks like this :

="Task Tracker for "&'READ ME!'!A16

The actual title of that sheet is Task Tracker for Property A. The Property A part is coming from &'READ ME!'!A16, which is another sheet and the cell in that sheet. Property A is the only thing that would be changing in the title and in order to change it I would go to the sheet called Read Me! and changed it there. It then automatically changes cell A1 in the other sheet to whatever I changed it to. So my goal is to also get the corresponding sheet name to change. Looking at it now, the sheet name is currently titled just Property A, so if there is a way I could link the sheet name to the correct cell from the Read Me! sheet, that would work better. All of the sheets relate back to the sheet called Read Me. (I did not make up this workbook, someone else did and I am trying to help them out).