renaming excel worksheets

[ljv6]

I need to rename worksheets each year updating for current year monthly Sunday dates - (each sheet is named Jan 1, Jan 8, Jan 15 and so on) is there is quick way to update without clicking on each sheet to update name? Although I use macros that have been written for me - i don't know how to write one - so please be very specific if you can help. Thanks!

[Sisyphus]

Hi,

This shouldn't be too hard but you need to be more precise. What is the implication of "renaming". Do you have 52 sheets and it doesn't matter which sheet gets which name? Or is there a particular order where last year's first Sunday should be come next year's fist Sunday etc?

What happens if Jan 1 is a Sunday and you end up with a Sunday more or less than the sheets you have? What happens if a sheet is missing in your collection and you forgot all about it but, now that I point it out, you remember that you went on holiday that time?

It's a long time until next year's Sunday. No need to rush.
Regards!

[User_5]

This code will add all 53 worksheets with Sunday date names for 2012. There are 53 Sundays in 2012.
You can only run this once per workbook or you'll get a run-time error.
Open Excel and press Alt+F11 to open the VB Editor. Insert a new module and
Paste this code (in a blank workbook) to try it out .

Code: Select all

Sub AddWeeklyWorksheets2012()
  Dim i As Integer
  Dim ws As Worksheet
  Dim strSheetName As String
'----- Add Jan Sheets -----'
    Dim arrJan() As Variant
    arrJan = Array(1, 8, 15, 22, 29)
      For i = 0 To UBound(arrJan)
      Set ws = ThisWorkbook.Worksheets.Add
      ws.Name = "Jan " & arrJan(i)
    Next i
'----- Add Feb Sheets -----'
    Dim arrFeb() As Variant
    arrFeb = Array(5, 12, 19, 26)
    For i = 0 To UBound(arrFeb)
      Set ws = ThisWorkbook.Worksheets.Add
      ws.Name = "Feb " & arrFeb(i)
    Next i
 '----- Add Mar Sheets -----'
    Dim arrMar() As Variant
    arrMar = Array(4, 11, 18, 25)
    For i = 0 To UBound(arrMar)
      Set ws = ThisWorkbook.Worksheets.Add
      ws.Name = "Mar " & arrMar(i)
    Next i
'----- Add Apr Sheets -----'
    Dim arrApr() As Variant
    arrApr = Array(1, 8, 15, 22, 29)
    For i = 0 To UBound(arrApr)
      Set ws = ThisWorkbook.Worksheets.Add
      ws.Name = "Apr " & arrApr(i)
    Next i
'----- Add May Sheets -----'
    Dim arrMay() As Variant
    arrMay = Array(6, 13, 20, 27)
    For i = 0 To UBound(arrMay)
      Set ws = ThisWorkbook.Worksheets.Add
      ws.Name = "May " & arrMay(i)
    Next i
'----- Add Jun Sheets -----'
    Dim arrJun() As Variant
    arrJun = Array(3, 10, 17, 24)
    For i = 0 To UBound(arrJun)
      Set ws = ThisWorkbook.Worksheets.Add
      ws.Name = "Jun " & arrJun(i)
    Next i
'----- Add Jul Sheets -----'
    Dim arrJul() As Variant
    arrJul = Array(1, 8, 15, 22, 29)
    For i = 0 To UBound(arrJul)
      Set ws = ThisWorkbook.Worksheets.Add
      ws.Name = "Jul " & arrJul(i)
    Next i
'----- Add Aug Sheets -----'
    Dim arrAug() As Variant
    arrAug = Array(5, 12, 19, 26)
    For i = 0 To UBound(arrAug)
      Set ws = ThisWorkbook.Worksheets.Add
      ws.Name = "Aug " & arrAug(i)
    Next i
'----- Add Sep Sheets -----'
    Dim arrSep() As Variant
    arrSep = Array(2, 9, 16, 23, 30)
    For i = 0 To UBound(arrSep)
      Set ws = ThisWorkbook.Worksheets.Add
      ws.Name = "Sep " & arrSep(i)
    Next i
'----- Add Oct Sheets -----'
    Dim arrOct() As Variant
    arrOct = Array(7, 14, 21, 28)
    For i = 0 To UBound(arrOct)
      Set ws = ThisWorkbook.Worksheets.Add
      ws.Name = "Oct " & arrOct(i)
    Next i
'----- Add Nov Sheets -----'
    Dim arrNov() As Variant
    arrNov = Array(4, 11, 18, 25)
    For i = 0 To UBound(arrNov)
      Set ws = ThisWorkbook.Worksheets.Add
      ws.Name = "Nov " & arrNov(i)
    Next i
'------ Add Dec Sheets -----'
   Dim arrDec() As Variant, SheetName
   arrDec = Array(2, 9, 16, 23, 30)
   For i = 0 To UBound(arrDec)
      Set ws = ThisWorkbook.Worksheets.Add
      ws.Name = "Dec " & arrDec(i)
   Next i
End Sub


I can show you the code for having the flexibilty to add months individually as you need them if you need that.
Its easy-to-understand code and won't let you add duplicate sheetnames and has some comments.
You won't get any error messages even if you have duplicate sheet names.Reply if you want to see that code.

[ljv6]

Thank you, thank you - worked great - question: it entered the sheets in reverse (starting in Dec and ending Jan - left to right) how can i put in Jan-Dec order and while i have you - can you tell me which part of the code i should edit for each year going forward?

[Don]

After a quick glance at part of the macro, I'd say you would have to switch each section in the macro and put it into reverse order.

So, for each section like this: '----- Add Jan Sheets -----' just move it around so December starts first and then go backwards to January. You are basically just rearranging the order the code in the macro runs.

[Sisyphus]

Hi,
Switching the sections will still leave the sheets in reversed order within each month.
But if you change this line

Code: Select all

Set ws = ThisWorkbook.Worksheets.Add


to this line:

Code: Select all

Set ws = ThisWorkbook.Worksheets.Add(Before:=Sheets(1))


for as many times as the line occurs, each sheet will be created before the previous instead of after it.

There are 12 occurrences, and you can make the change by using Find / Replace.

[User_5]

Hi,

I haven't had time to visit the forums lately and so I did not see your response until today.
I'm sorry for any diffulty you have had as a result of the code I sent you.
The following code will insert worksheets with Sunday dates reading left-to-right for 2012.
In other words, January will be the left-most worksheet and December the last worksheet.
The code guards against duplicate sheet names so you shouldn't get any run-time errors.
You can insert all of the sheets for the entire year by running the first macro.
Or, if you wish, you can run each month's macro seperately as the year progresses.
Just be sure to run them in order. March won't run unless you have already run Jan. and Feb.
Hopefully this is what you're looking for. See my next post for instructions going forward
for future years.

Code: Select all

'Module-wide vars
Dim i As Integer
Dim ws As Worksheet
Dim strSheetName As String
Dim tw As Workbook
Sub AddAllWeeklySheets2012()
'Click inside this macro and press F5 to add all
'sheets for the whole year.
  AddJanSheets
  AddFebSheets
  AddMarSheets
  AddAprSheets
  AddMaySheets
  AddJunSheets
  AddJulSheets
  AddAugSheets
  AddSepSheets
  AddOctSheets
  AddNovSheets
  AddDecSheets
End Sub
'***************************************************************
'Run procedures individually to add worksheets for a given month.
'Nothing will happen if you have run the macro above because of
'duplicate sheet names.
'***************************************************************
Sub AddJanSheets()
  Dim arrJan() As Variant
  'Store the dates of each Sunday in January 2012 in an array
  arrJan = Array(29, 22, 15, 8, 1)
  'Add a worksheet for each Sunday in the array
  For i = 0 To UBound(arrJan)
    strSheetName = "Jan " & arrJan(i)
    'Check if the worksheet name already exists
    Call Dup_Name(strSheetName)
    'If yes, then don't add a sheet and exit the sub
    If Dup_Name(strSheetName) = True Then Exit Sub
    'Otherwise add a new sheet
    Set tw = ThisWorkbook
    Set ws = tw.Worksheets.Add
    'Give the worksheet a name that corresponds to
    'each January Sunday's date
    ws.Name = strSheetName
    'Repeat until the upper boundary of the array is reached
  Next i
End Sub
Private Sub AddFebSheets()
  Dim arrFeb() As Variant
  arrFeb = Array(26, 19, 12, 5)
  For i = 0 To UBound(arrFeb)
    strSheetName = "Feb " & arrFeb(i)
    Call Dup_Name(strSheetName)
    If Dup_Name(strSheetName) = True Then Exit Sub
    Set tw = ThisWorkbook
    Set ws = tw.Worksheets.Add(after:=Sheets("Jan 29"))
    ws.Name = strSheetName
  Next i
End Sub
Sub AddMarSheets()
  Dim arrMar() As Variant
  arrMar = Array(25, 18, 11, 4)
  For i = 0 To UBound(arrMar)
    strSheetName = "Mar " & arrMar(i)
    Call Dup_Name(strSheetName)
    If Dup_Name(strSheetName) = True Then Exit Sub
    Set tw = ThisWorkbook
    Set ws = tw.Worksheets.Add(after:=Sheets("Feb 26"))
    ws.Name = strSheetName
  Next i
End Sub
Sub AddAprSheets()
  Dim arrApr() As Variant
  arrApr = Array(29, 22, 15, 8, 1)
  For i = 0 To UBound(arrApr)
    strSheetName = "Apr " & arrApr(i)
    Call Dup_Name(strSheetName)
    If Dup_Name(strSheetName) = True Then Exit Sub
    Set tw = ThisWorkbook
    Set ws = tw.Worksheets.Add(after:=Sheets("Mar 25"))
    ws.Name = strSheetName
  Next i
End Sub
Sub AddMaySheets()
  Dim arrMay() As Variant
  arrMay = Array(27, 20, 13, 6)
  For i = 0 To UBound(arrMay)
    strSheetName = "May " & arrMay(i)
    Call Dup_Name(strSheetName)
    If Dup_Name(strSheetName) = True Then Exit Sub
    Set tw = ThisWorkbook
    Set ws = tw.Worksheets.Add(after:=Sheets("Apr 29"))
    ws.Name = strSheetName
  Next i
End Sub
Sub AddJunSheets()
  Dim arrJun() As Variant
  arrJun = Array(24, 17, 10, 3)
  For i = 0 To UBound(arrJun)
    strSheetName = "Jun " & arrJun(i)
    Call Dup_Name(strSheetName)
    If Dup_Name(strSheetName) = True Then Exit Sub
    Set tw = ThisWorkbook
    Set ws = tw.Worksheets.Add(after:=Sheets("May 27"))
    ws.Name = strSheetName
  Next i
End Sub
Sub AddJulSheets()
  Dim arrJul() As Variant
  arrJul = Array(29, 22, 15, 8, 1)
  For i = 0 To UBound(arrJul)
    strSheetName = "Jul " & arrJul(i)
    Call Dup_Name(strSheetName)
    If Dup_Name(strSheetName) = True Then Exit Sub
    Set tw = ThisWorkbook
    Set ws = tw.Worksheets.Add(after:=Sheets("Jun 24"))
    ws.Name = strSheetName
  Next i
End Sub
Sub AddAugSheets()
  Dim arrAug() As Variant
  arrAug = Array(26, 19, 12, 5)
  For i = 0 To UBound(arrAug)
    strSheetName = "Aug " & arrAug(i)
    Call Dup_Name(strSheetName)
    If Dup_Name(strSheetName) = True Then Exit Sub
    Set tw = ThisWorkbook
    Set ws = tw.Worksheets.Add(after:=Sheets("Jul 29"))
    ws.Name = strSheetName
  Next i
End Sub
Sub AddSepSheets()
  Dim arrSep() As Variant
  arrSep = Array(30, 23, 16, 9, 2)
  For i = 0 To UBound(arrSep)
    strSheetName = "Sep " & arrSep(i)
    Call Dup_Name(strSheetName)
    If Dup_Name(strSheetName) = True Then Exit Sub
    Set tw = ThisWorkbook
    Set ws = tw.Worksheets.Add(after:=Sheets("Aug 26"))
    ws.Name = strSheetName
  Next i
End Sub
Sub AddOctSheets()
  Dim arrOct() As Variant
  arrOct = Array(28, 21, 14, 7)
  For i = 0 To UBound(arrOct)
    strSheetName = "Oct " & arrOct(i)
    Call Dup_Name(strSheetName)
    If Dup_Name(strSheetName) = True Then Exit Sub
    Set tw = ThisWorkbook
    Set ws = tw.Worksheets.Add(after:=Sheets("Sep 30"))
    ws.Name = strSheetName
  Next i
End Sub
Sub AddNovSheets()
  Dim arrNov() As Variant
  arrNov = Array(25, 18, 11, 4)
  For i = 0 To UBound(arrNov)
    strSheetName = "Nov " & arrNov(i)
    Call Dup_Name(strSheetName)
    If Dup_Name(strSheetName) = True Then Exit Sub
    Set tw = ThisWorkbook
    Set ws = tw.Worksheets.Add(after:=Sheets("Oct 28"))
    ws.Name = strSheetName
  Next i
End Sub
Sub AddDecSheets()
  Dim arrDec() As Variant
  arrDec = Array(30, 23, 16, 9, 2)
  For i = 0 To UBound(arrDec)
    strSheetName = "Dec " & arrDec(i)
    Call Dup_Name(strSheetName)
    If Dup_Name(strSheetName) = True Then Exit Sub
    Set tw = ThisWorkbook
    Set ws = tw.Worksheets.Add(after:=Sheets("Nov 25"))
    ws.Name = strSheetName
  Next i
End Sub
Private Function Dup_Name(ShtName As String) As Boolean
'Checks to see if a worksheet name already exists.
  Dim Sht As Worksheet
  'Loop through the sheets.
  For Each Sht In ThisWorkbook.Worksheets
    'Does the sheet name exist?
    If Sht.Name = ShtName Then
    'If yes, return True, do not add a sheet and exit the function.
      Dup_Name = True
      Exit Function
    'If no, return False to the calling procedure, which will add a new sheet.
    Else: Dup_Name = False
    End If
  'Repeat until all sheets have been ID'd.
  Next Sht
End Function


[Sisyphus]

Hi,
Here is a somewhat shorter solution. You can use this one any year in the preesent, past or future:

Code: Select all

Option Explicit

    Const ThisYear As Long = 2012
    Const StartDay As Long = vbSunday
    Const LastSheetOnLeft As Boolean = True
   
Public Sub SetupSheets()

    Dim Ws As Worksheet
    Dim SheetDate As Date
   
    SheetDate = DateSerial(ThisYear, 1, 1)
    Do While Weekday(SheetDate) <> StartDay
        SheetDate = SheetDate + 1
    Loop
   
    Do
        If LastSheetOnLeft Then
            Set Ws = Sheets.Add(Before:=Sheets(1))
        Else
            Set Ws = Sheets.Add(After:=Sheets(Sheets.Count))
        End If
        Ws.Name = UCase(Format(SheetDate, "mmm dd"))
        SheetDate = SheetDate + 7
    Loop While Year(SheetDate) = ThisYear
End Sub


To run it you should set the three constants at the top of the code. You can pick any day of the week to start. Also note the first line of executable code:
SheetDate = DateSerial(ThisYear, 1, 1)
The code start with this date, looks for the first StartDay, and sets up sheets until the end of the year. If you change the above line to
SheetDate = DateSerial(ThisYear, 6, 1)
It would start looking from June 1, find that Jun 3 is the first Sunday and start creating worksheets from there.

Originally you asked for renaming worksheets. Have you given up on that idea?

[User_5]

Hello lvj6,

Here is some simple code to get the job done.
Simply change the userYear value where it shows you to in the code.
It works for all years, including leap years.

You also have the option to append the year to each sheet name.
Just set ShowYear to either True or False.
You don't have to use this option. Just leave it as is if you want.
I have it set to False.

If you do want to show the year in the sheet name, set ShowYear to True.
If you don't want to show the year set ShowYear to False.

Code: Select all

Sub AddSundayWorksheets()
  Dim i As Integer
  Dim j As Integer
  Dim userYear As Integer
  Dim newMonth As Integer
  Dim ws As Worksheet
  Dim Sundays As Collection
  Dim DVal As Date
  Dim ShowYear As Boolean
  Dim Months()  As Variant 'Array that stores the months
 
 
  '-Change the FOUR-DIGIT number to the desired year---------
       
        userYear = 2012
 
  '-----------------------------------------------------------

  '-Change to True or False to append the year to sheet name--
         
        ShowYear = False
 
  '-------------------------------------------------------------
  Set ws = ThisWorkbook.Worksheets.Add
  ws.Name = "x"
  Months = Array("Jan", "Feb", "Mar", "Apr", "May", "Jun", "Jul", "Aug", "Sep", "Oct", "Nov", "Dec")
 
  DVal = DateValue("Jan 1," & userYear) - 1
   
  'Build a Collection of all Sunday dates for the given year
  Set Sundays = New Collection
  For j = 1 To 365
    DVal = DVal + 1
     If WeekDay(DVal) = 1 Then
       Sundays.Add DVal
    End If
  Next j

  'Add Sheets and name them
  For i = 1 To Sundays.Count
    newMonth = Month(Sundays(i))
    If ShowYear = True Then
      Set ws = ThisWorkbook.Worksheets.Add(before:=Worksheets("x"))
      ws.Name = Months(newMonth - 1) & " " & Day(Sundays(i)) & " " & userYear
    Else
      Set ws = ThisWorkbook.Worksheets.Add(before:=Worksheets("x"))
      ws.Name = Months(newMonth - 1) & " " & Day(Sundays(i)) & " "
    End If
    Next i
 
  Application.DisplayAlerts = False
  Worksheets("x").Delete
  Application.DisplayAlerts = True
  Worksheets(1).Select
  Set Sundays = Nothing

End Sub


[Sisyphus]

Hello User_5,
This is a very interesting approach. I like it. But you over-state its capability to deal with all kinds of years. There is no assurance that 365 days will always include all Sundays in a year because leap years have 366 days.

Your loop that creates and names the worksheets seems a bit laborious. What do you think of this construct?

Code: Select all

  For i = 1 To Sundays.Count
    newMonth = Month(Sundays(i))
    Set ws = ThisWorkbook.Worksheets.Add(before:=Worksheets("x"))
    ws.Name = Months(newMonth - 1) & " " & Day(Sundays(i)) & _
                     Iif(userYear, " " & userYear, "")
  Next i