Macro to rename tabs from a control sheet

[tennisfreak]

Hi all,

Sorry for a n00b question. I have a control sheet and many other sheets that accumulate into the control sheet. But the sheet names always change, so is it possible to create a macro that updates the tabs to reflect the names from the control sheet?

 

[dave3009]

Hi tennisfreak,

I think, depending on a few things, this can be done. Under what condition do the sheet names 'always change', how do the cells in the control sheet relate to the sheet tabs. Under what conditions would the sheet tabs be reassigned or control sheet updated? Might it be easier just to try preventing the sheets from changing names rather than trying to keep track of when and how the sheets are being renamed?

Thanks

Dave

 

[DILIPandey]

Hi Tennisfreak,

Yes.. macro can change the sheet names but before that, what is the purpose (reason) because of which these sheet name are getting changed ?
and then, will it not create a problem when all sheets names will be changed from control sheet ? or you will first update sheet names in control sheet or something like that ?

Regards,
DILIPandey

 

[tennisfreak]

Thank you both for replying. So here is the background info, every 6 months I have tennis matches (hence the username) at other clubs that rotate. Each match is then recorded on separate sheets and then compiled into the control sheet.
ex. Cells B1:G1 have all the names of the clubs and I want each cell to relate to the sheet?

Does any of this make any sense? haha

 

[dave3009]

Hi tennisfreak,

It sort of makes sense. Though a snapshot of how the layout looks would be very useful so we can assist you finding the exact solution. The thing is if you have a formula on your sheet that contains a reference to another sheet (might look like Wimbeldon!$A$1:$S$50) then if you change the sheet name the formula would update itself. I'm just a little confused as to how you link the Control Sheet cells B1:G1 to each sheet in the book. I'm sure there will be other questions as this grows arms and legs but hopefully with an idea of how it actually looks we might be able to help you set it up so it doesn't get out of hand

Regards

Dave

p.s. there is a link to some useful tools in my signature block.

 

[tennisfreak]

I have included a screenshot to illustrate

So place 1 through 14 appear on the control sheet at the top (cells B1:O1) and i want each cell to be able to change the name of the tab when I edit the cells B1:O1

 

[My Aswer Is This]

Try this:
To install this code:

Right-click on the "Control" sheet tab
Select View Code from the pop-up context menu
Paste the code in the VBA edit window
Then as you enter names in Row (1) your sheet names should change.
Be sure and create the blank sheets before running this script.

Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Target, Range("B1:O1")) Is Nothing Then
On Error GoTo J
Sheets(Target.Column).Name = Target.Value
End If
Exit Sub
J:
Msgbox "That sheet name already exist or is a invalid name"
Target.Value = "Error"
End Sub