Excel Query - Renaming sheets

[Rich_L]

Righty ho, have no clue about excel macros and suchlike so figured why not ask here!

Basically I'm preparing an excel workbook with monthly target sheets in, with a different sheet for each month. Whilst it would be easy to have Month 1,2,3,4 etc, that's apparently too hard o) and the sheets need to be named after the actual months, but people have different start dates and so different start months.

I was thinking I could have a cell with Month 1 (say November) generate a 12 month list and then run a macro to apply that list to the sheet names, however I've no idea how to do this...!

Any ideas if this is possible much much much appreciated!

Cheers!

 

[Murf]

Private Sub sheet_names()

''Renames Sheets with starting month specified in cell

''Freezes screen so changes aren't apparent
Application.ScreenUpdating = False


Dim i As Integer


''Resets names to 1 through 12 to avoid duplicates when renaming a 2nd time
For i = 1 To 12
Worksheets(i).Name = i
Next i


''Takes starting month, finds it in hidden list and returns column number
Dim startCell As Integer
Sheet14.Select
startCell = Cells.Find(What:=Sheet13.Range("a1").Value, After:=Sheet14.Cells(1, 1), LookIn:=xlValues, LookAt _
:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:= _
False).Column

''Names sheets in chronological order beginning with specified start month
For i = 0 To 11
Worksheets(i + 1).Name = Sheet14.Cells(1, startCell + i).Value
Next i


''Unfreezes screen so changes are apparent
Application.ScreenUpdating = True


End Sub


You should find that the above code will do the job. Just drop it into a module in your workbook and assign it to a button.


It works on the premise that you have 14 sheets, first 12 for the months, a 13th where the start month is specified and a 14th which on row 1 has a list of the months in the year, that repeats once. ie – Column A is January, B is February, L is December and M is January all the way to December in X. To create this list type January in A1 and fill the series along to X. You’ll notice the search string is referencing Sheet13, range A1, this can be changed to wherever the starting month is typed, and the list can go anywhere too, as long as you update the code accordingly. It will rename as many times as you like as I’ve put a reset into the code so when the macro is run it wipes out the names and resets them to 1-12. Otherwise you’d get duplication errors when it started re-arranging the order.



Hope this is ok, if not tell me what you need.

 

[mark66]

Here is another way....

No need for sheets with names on, no need to create them and then rename them, etc. Create the following as a new Macro and run:

Sub InsertMonthSheets()
Dim i As Integer, j As Integer
i = InputBox("Please enter the first month as a number, " & vbCrLf & "1 = January, 2=February, etc")
For j = 0 To 11
Sheets.Add After:=Worksheets(Worksheets.Count)
Sheets(Worksheets.Count).Name = Format$(DateValue("1/" & (i + j - 1) Mod 12 + 1 & "/2006"), "mmmm")
Next j
End Sub

This will work in any spreadsheet, regardless of settings, provided there is no sheet named with a date in it already.

 

[Murf]

Here is another way....

No need for sheets with names on, no need to create them and then rename them, etc. Create the following as a new Macro and run:

Sub InsertMonthSheets()
Dim i As Integer, j As Integer
i = InputBox("Please enter the first month as a number, " & vbCrLf & "1 = January, 2=February, etc")
For j = 0 To 11
Sheets.Add After:=Worksheets(Worksheets.Count)
Sheets(Worksheets.Count).Name = Format$(DateValue("1/" & (i + j - 1) Mod 12 + 1 & "/2006"), "mmmm")
Next j
End Sub

This will work in any spreadsheet, regardless of settings, provided there is no sheet named with a date in it already.

I was working on the presumption there was content in the sheets, if not this is much simpler.