Updating Worksheet Names for Rolling-12 Report

[Guest]

I have 13 worksheets in my workbook. For Sheet2 to Sheet13, I want the sheet
name to reflect its own cell A5. I have code in Sheet1 to accomplish this
task for the subsequent 12 sheets, but currently it only changes the 2nd
worksheet name and I can't figure out why. See below:

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
Dim i As Long
Dim Counter As Long
Let Counter = Worksheets.Count
On Error Resume Next
For i = 2 To Counter
Sheets(i).Name = Cells(5, 1).Value
Next i
On Error GoTo 0
End Sub

How can I get the code to update all 12 worksheet names? Here's some
reference information to see how the names are derived from the target cells:

Sheet1 is named "Settings" and is the only sheet with a fixed name. Because
workbooks are based on a rolling 12 month period, cell B17 is a variable
month field currently set to "January" and B18 is a variable year field
currently set to "2005".

Cell A5 of Sheet1 is =B17&" "&B18
Cell A5 of Sheet2 is ="Settings!B17&" "&Settings!B18"+0
Cell A5 of Sheet3 is =DATEVALUE(Settings!B17&Settings!B18)+32
Cell A5 of Sheet4 is =DATEVALUE(Settings!B17&Settings!B18)+64
Cell A5 of Sheet5 is =DATEVALUE(Settings!B17&Settings!B18)+96

Cell A5 of Sheet6 to Sheet13 is the same, each with a higher multiple of 32
so that it displays the next month in sequence.

Any Ideas on how to make this work?

Thanks,
Damian Carrillo

 

[Guest]

Here is some Code that I use for a rolling 12. The trick to being able to use
this code is that you have to change the Code name of each of the sheets in
the workbook. In the VB Editor (Alt F11) you need to show the Properties
screen (View -> Properties) You will now have a perperties window that will
allow you to change the name of the sheets Sheet1 changes to shtJan or
however you have it set up. Repeat this for all of the month sheets. Plce
this code in the ThisWorkbook -> On_Open event and Bob's your uncle. The
Months display as Jan 2005, Feb 2005... It also moves the current month to
the front of the workbook when the moth rolls over...

Dim intMonthNumber As Integer

'Initialize Local Variables
intMonthNumber = Month(Now())

If intMonthNumber >= 1 Then
shtJan.Move After:=shtStart
shtJan.Name = "Jan " & Year(Now())
shtJan.Select
Else
shtJan.Name = "Jan " & Year(Now()) - 1
End If
If intMonthNumber >= 2 Then
shtFeb.Move After:=shtStart
shtFeb.Name = "Feb " & Year(Now())
shtFeb.Select
Else
shtFeb.Name = "Feb " & Year(Now()) - 1
End If
If intMonthNumber >= 3 Then
shtMar.Move After:=shtStart
shtMar.Name = "Mar " & Year(Now())
shtMar.Select
Else
shtMar.Name = "Mar " & Year(Now()) - 1
End If
If intMonthNumber >= 4 Then
shtApr.Move After:=shtStart
shtApr.Name = "Apr " & Year(Now())
shtApr.Select
Else
shtApr.Name = "Apr " & Year(Now()) - 1
End If
If intMonthNumber >= 5 Then
shtMay.Move After:=shtStart
shtMay.Name = "May " & Year(Now())
shtMay.Select
Else
shtMay.Name = "May " & Year(Now()) - 1
End If
If intMonthNumber >= 6 Then
shtJun.Move After:=shtStart
shtJun.Name = "Jun " & Year(Now())
shtJun.Select
Else
shtJun.Name = "Jun " & Year(Now()) - 1
End If
If intMonthNumber >= 7 Then
shtJul.Move After:=shtStart
shtJul.Name = "Jul " & Year(Now())
shtJul.Select
Else
shtJul.Name = "Jul " & Year(Now()) - 1
End If
If intMonthNumber >= 8 Then
shtAug.Move After:=shtStart
shtAug.Name = "Aug " & Year(Now())
shtAug.Select
Else
shtAug.Name = "Aug " & Year(Now()) - 1
End If
If intMonthNumber >= 9 Then
shtSep.Move After:=shtStart
shtSep.Name = "Sep " & Year(Now())
shtSep.Select
Else
shtSep.Name = "Sep " & Year(Now()) - 1
End If
If intMonthNumber >= 10 Then
shtOct.Move After:=shtStart
shtOct.Name = "Oct " & Year(Now())
shtOct.Select
Else
shtOct.Name = "Oct " & Year(Now()) - 1
End If
If intMonthNumber >= 11 Then
shtNov.Move After:=shtStart
shtNov.Name = "Nov " & Year(Now())
shtNov.Select
Else
shtNov.Name = "Nov " & Year(Now()) - 1
End If
If intMonthNumber >= 12 Then
shtDec.Move After:=shtStart
shtDec.Name = "Dec " & Year(Now())
shtDec.Select
Else
shtDec.Name = "Dec " & Year(Now()) - 1
End If


HTH...

 

[Guest]

Thanks for the code, Jim, but I did find an easier solution within the code I
originally posted. See below:

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
Dim i As Long
Dim Counter As Long
Let Counter = Worksheets.Count
On Error Resume Next 'in case invalid sheet name
For i = 2 To Counter
Sheets(i).Name = Sheets(i).Cells(5, 1).Value
Next i
On Error GoTo 0
End Sub

My problem was that I was incrementing the sheet name, but not the value, so
the values would remain unchanged. Or so my thoughts. Point being it works
like a charm now! The only odd thing is that if I change the starting month
to, say July, I have to input the year again too or it won't refresh all 12
name tabs correctly. Is there any way to emulate a reenter of the same date
to get around this issue?

Thanks again for your suggestion,
Damian Carrillo