Forum Discussion

aprilrice's avatar
aprilrice
Copper Contributor
Sep 05, 2023

Sheet tabs in Excel

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.

@april.rice@svz

Excel
  • HansVogelaar's avatar
    HansVogelaar
    MVP
    Sep 05, 2023

    aprilrice 

    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)
    Do
        dtm = Application.WorkDay(dtm, 1)
        If Month(dtm) <> mnt Then Exit Do
        Set wsh = wbk.Worksheets.Add(After:=wsh)
        wsh.Name = dtm
    Loop
    Application.DisplayAlerts = False
    wbk.Worksheets(1).Delete
    Application.DisplayAlerts = True
    Application.ScreenUpdating = True
    Exit Sub
ErrHandler:
    Beep
End Sub
    • aprilrice's avatar
      aprilrice
      Copper Contributor
      Sep 06, 2023

      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 youHansVogelaar 

      • HansVogelaar's avatar
        HansVogelaar
        MVP
        Sep 06, 2023

        aprilrice

        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.

         

Resources