Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Updating Worksheet Names for Rolling-12 Report
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Updating Worksheet Names for Rolling-12 Report
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... "Damian" wrote: 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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Updating Worksheet Names for Rolling-12 Report
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 "Jim Thomlinson" wrote: 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... ...<Message Clipped |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Updating Pivot Table report | Excel Discussion (Misc queries) | |||
Create auto updating data validation list from all worksheet names | Excel Worksheet Functions | |||
YTD value minus prev mth value = curent month - Rolling Report | Excel Worksheet Functions | |||
How do I set up a rolling monthly report in Excel? | Excel Discussion (Misc queries) | |||
return all worksheet tab names and chart sheet tab names in report - an example | Excel Programming |