Please Note: This article is written for users of the following Microsoft Excel versions: 2007, 2010, 2013, and 2016. If you are using an earlier version (Excel 2003 or earlier), this tip may not work for you. For a version of this tip written specifically for earlier versions of Excel, click here: Naming Tabs for Weeks.
by Allen Wyatt
(last updated June 24, 2017)
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.
ExcelTips is your source for cost-effective Microsoft Excel training. This tip (12403) applies to Microsoft Excel 2007, 2010, 2013, and 2016. You can find a version of this tip for the older menu interface of Excel here: Naming Tabs for Weeks.
Solve Real Business Problems Master business modeling and analysis techniques with Excel and transform data into bottom-line results. This hands-on, scenario-focused guide shows you how to use the latest Excel tools to integrate data from multiple tables. Check out Microsoft Excel 2013 Data Analysis and Business Modeling today!
Excel allows you to change the names assigned to the worksheets in a workbook. If you want to have those names appear in ...Discover More
Worksheets are easily accessible in a workbook, but you may not want them to be so open. You can hide worksheets so they ...Discover More
Want a quick way to insert a worksheet? There's nothing faster than using the handy shortcut.Discover More
FREE SERVICE: Get tips like this every week in ExcelTips, a free productivity newsletter. Enter your address and click "Subscribe."
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.