1. This site uses cookies. By continuing to use this site, you are agreeing to our use of cookies. Learn More.

Excel Query - Renaming sheets

Discussion in 'Windows & Other Software' started by Rich_L, 22 May 2006.

  1. Rich_L


    Joined: 18 Oct 2002

    Posts: 18,172

    Location: Santa Barbara, Californee

    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!

  2. Murf


    Joined: 18 Oct 2002

    Posts: 5,467

    Location: Newcastle

    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
    startCell = Cells.Find(What:=Sheet13.Range("a1").Value, After:=Sheet14.Cells(1, 1), LookIn:=xlValues, LookAt _
    :=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:= _

    ''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. :)
  3. mark66


    Joined: 1 May 2003

    Posts: 3,206

    Location: Bucks

    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.
  4. Murf


    Joined: 18 Oct 2002

    Posts: 5,467

    Location: Newcastle

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