Please Note: This article is written for users of the following Microsoft Excel versions: 97, 2000, 2002, and 2003. If you are using a later version (Excel 2007 or later), this tip may not work for you. For a version of this tip written specifically for later versions of Excel, click here: Naming Tabs for Weeks.
by Allen Wyatt
(last updated March 27, 2018)
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 (2018) applies to Microsoft Excel 97, 2000, 2002, and 2003. You can find a version of this tip for the ribbon interface of Excel (Excel 2007 and later) here: Naming Tabs for Weeks.
Save Time and Supercharge Excel! Automate virtually any routine task and save yourself hours, days, maybe even weeks. Then, learn how to make Excel do things you thought were simply impossible! Mastering advanced Excel macros has never been easier. Check out Excel 2010 VBA and Macros today!
If you have a lot of worksheets in a workbook, you may wonder if you can "freeze" the position of some of those worksheet ...Discover More
Need a quick way to have a worksheet for each day in a month? Here's a macro that makes the worksheet creation a snap.Discover More
Need a worksheet's name to change based on the contents of a cell? You'll need to rely on a macro to do the changing, but ...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 menu interface (Excel 97, Excel 2000, Excel 2002, or Excel 2003)? This site is for you! If you use a later version of Excel, visit our ExcelTips site focusing on the ribbon interface.