Sheet tabs in Excel

Copper Contributor

Hi. I'm trying to figure out a way to make a workbook in Excel where the sheet tabs on the bottom correspond with the workdays of the month. I'm currently having to enter them one by one each month and I'm trying to make it a little easier and less time consuming for myself. If anyone can point me in the right direction I would greatly appreciate it. Thanks.


3 Replies


The following macro will prompt for a date, then create a new workbook with one worksheet for each workday of that month.

If you save the macro in your personal macro workbook PERSONAL.XLSB it will always be available.

Sub WorkingDaysWorkbook()
    Dim dtm As Date
    Dim mnt As Long
    Dim wbk As Workbook
    Dim wsh As Worksheet
    On Error GoTo ErrHandler
    dtm = InputBox(Prompt:="Enter any date in the month")
    Application.ScreenUpdating = False
    mnt = Month(dtm)
    dtm = dtm - Day(dtm)
    Set wbk = Workbooks.Add(xlWBATWorksheet)
    Set wsh = wbk.Worksheets(1)
        dtm = Application.WorkDay(dtm, 1)
        If Month(dtm) <> mnt Then Exit Do
        Set wsh = wbk.Worksheets.Add(After:=wsh)
        wsh.Name = dtm
    Application.DisplayAlerts = False
    Application.DisplayAlerts = True
    Application.ScreenUpdating = True
    Exit Sub
End Sub

Thank you for your response. I don't quite understand how to do this. I have the developer tab open in excel. I went in to the Macros icon. There are no workbooks.  And I don't understand the code. I see that you have them numbered, does that mean that I put those in the corresponding cells? I'm really not that techy. lol. If you could expound on how to do this I would greatly appreciate it. Maybe break it down in laymeans terms if you wouldn't mind. Again, thank you@Hans Vogelaar 


The line numbers are not part of the code.

Select the code, from Sub up to and including End Sub - you'll see that the line numbers are not included.

Copy it (Ctrl+C)

Press Alt+F11 to activate the Visual Basic Editor.

Select Insert > Module or press Alt+I, M to create a new code module.

Paste the code into the module (Ctrl+V).

There are two ways you can run the code:

  1. In the Visual Basic Editor, click anywhere in the code, then select Run > Run Macro, or press F5.
  2. In Excel, press Alt+F8 to activate the Macros dialog. You should now see the name of the macro that you copied/pasted.
  3. Click Run.

I have attached a demo workbook with a command button that will run the macro.

You'll have to allow macros when you open the workbook.