HELP with Sheet names

[danaeann]

I am working on a spreadsheet that has a master sheet named weekly chart and a lot of other sheets named by dates. On the weekly chart sheet I have dates that I change each time I send the spreadsheet out. When I change these dates I want it to change all the sheet names on the remaining sheets. I am new to VBA codes and can't figure out what code to use and how to make it update when I change the dates. An example spreadsheet would be awesome if someone could e-mail one to me!!! Thank you!

 

[jim may]

Hi danaeann and welcome to the Board.

Excel "spits-up" if you try and name a sheet with a date including the "/" separator.. So If you don't mind your sheets with your dates like 12-4-2015, 12-11-2015, etc then here's a way to do what you want...

Here a SAMPLE FILE as you asked for.. Beginning with your Master Sheet..
Excel 2012

	A	B

<tbody>
[TD="align: center"]1[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]2[/TD]
[TD="align: right"][/TD]
[TD="bgcolor: #F2F2F2, align: center"]My Current[/TD]

[TD="align: center"]3[/TD]
[TD="align: right"][/TD]
[TD="bgcolor: #F2F2F2, align: center"]Sheet Name[/TD]

[TD="align: center"]4[/TD]
[TD="align: right"][/TD]
[TD="bgcolor: #F2F2F2, align: center"]Dates[/TD]

[TD="align: center"]5[/TD]
[TD="align: right"][/TD]
[TD="align: right"]12/4/2015[/TD]

[TD="align: center"]6[/TD]
[TD="align: right"][/TD]
[TD="align: right"]12/11/2015[/TD]

[TD="align: center"]7[/TD]
[TD="align: right"][/TD]
[TD="align: right"]12/18/2015[/TD]

[TD="align: center"]8[/TD]
[TD="align: right"][/TD]
[TD="align: right"]12/25/2015[/TD]

[TD="align: center"]9[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]10[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

</tbody>

Weekly Chart​

Switch to the VBA App and in the WeeklyChart Code Window (Not a Standard Module Window) Paste in the following Code:

Private Sub Worksheet_Change(ByVal Target As Range)
If Application.Intersect(Target, Range("B5:B8")) Is Nothing Then
Exit Sub
End If
Select Case Target.Address
    Case Is = "$B$5"
    Worksheets(2).Name = Replace(Target.Value, "/", "-")
    Case Is = "$B$6"
        Worksheets(3).Name = Replace(Target.Value, "/", "-")
    Case Is = "$B$7"
        Worksheets(4).Name = Replace(Target.Value, "/", "-")
    Case Is = "$B$8"
        Worksheets(5).Name = Replace(Target.Value, "/", "-")
End Select
End Sub

Be sure to Create as many sheets (beforehand) on the number of sheets you refer to in your Weekly Chart Sheet (4 currently)...

You could HIDE the Weekly Chart Sheet so it is not VISIBLE to the Users --

Test it out by entering NEW DATES in any of THE 4 CELLS ON YOUR WEELKY CHART SHEET - Watch the corresponding Sheet Names change as you do...
Maybe this will get you started... Jim

 

[danaeann]

Yes that works! Thank you so much! The only issue I am having now is that when I change the start date in b5 and auto fill the remaining 120 days it wont update the sheet names. But if I manually change each date it does update the sheet name. Is there a way to use the code by autofilling the dates in b5-b125 without manually entering each date?