Written by Allen Wyatt (last updated December 24, 2022)
This tip applies to Excel 2007, 2010, 2013, 2016, 2019, 2021, and Excel in Microsoft 365
When you are starting a new workbook, one common scenario calls for creating a year's worth of worksheets, one for each week of the year. In other words, a workbook could end up containing 52 or 53 worksheets, depending on how many weeks there are in a particular year.
If you have a need to create such a workbook, you know that individually creating and naming all the worksheets can be a real hassle. This is where a macro would come in handy. The following macro will add the appropriate number of worksheets, and then rename all of the worksheets according to week number (01 through 52).
Sub YearWorkbook1() Dim iWeek As Integer Dim sht As Variant Application.ScreenUpdating = False Worksheets.Add After:=Worksheets(Worksheets.Count), _ Count:=(52 - Worksheets.Count) iWeek = 1 For Each sht In Worksheets sht.Name = "Week " & Format(iWeek, "00") iWeek = iWeek + 1 Next sht Application.ScreenUpdating = True End Sub
If you instead need a way to create worksheets that show the ending date of each week for a year, then a different macro is needed.
Sub YearWorkbook2() Dim iWeek As Integer Dim sht As Variant Dim sTemp As String Dim dSDate As Date sTemp = InputBox("Date for the first worksheet:", "End of Week?") dSDate = CDate(sTemp) Application.ScreenUpdating = False Worksheets.Add After:=Worksheets(Worksheets.Count), _ Count:=(52 - Worksheets.Count) For Each sht In Worksheets sht.Name = Format(dSDate, "dd-mmm-yyyy") dSDate = dSDate + 7 Next sht Application.ScreenUpdating = True End Sub
This version of the macro asks you for a beginning date. It then uses that date to start naming the different worksheets in the workbook. If you enter a value that cannot be translated to a date, then the macro will generate an error.
Note:
ExcelTips is your source for cost-effective Microsoft Excel training. This tip (12403) applies to Microsoft Excel 2007, 2010, 2013, 2016, 2019, 2021, and Excel in Microsoft 365. You can find a version of this tip for the older menu interface of Excel here: Naming Tabs for Weeks.
Program Successfully in Excel! John Walkenbach's name is synonymous with excellence in deciphering complex technical topics. With this comprehensive guide, "Mr. Spreadsheet" shows how to maximize your Excel experience using professional spreadsheet application development tips from his own personal bookshelf. Check out Excel 2013 Power Programming with VBA today!
Microsoft added a new feature to Excel that causes a "lock icon" to appear at the left of a worksheet tab if the ...
Discover MoreExcel places a limit on how many characters you can use in a worksheet name. This tip discusses that limit and provides ...
Discover MoreExcel allows you to easily add and remove worksheets from a workbook. You may want a way to automatically rename all of ...
Discover MoreFREE SERVICE: Get tips like this every week in ExcelTips, a free productivity newsletter. Enter your address and click "Subscribe."
There are currently no comments for this tip. (Be the first to leave your comment—just use the simple form above!)
Got a version of Excel that uses the ribbon interface (Excel 2007 or later)? This site is for you! If you use an earlier version of Excel, visit our ExcelTips site focusing on the menu interface.
FREE SERVICE: Get tips like this every week in ExcelTips, a free productivity newsletter. Enter your address and click "Subscribe."
Copyright © 2025 Sharon Parq Associates, Inc.
Comments