Forum Discussion
Sheet tabs in Excel
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
- aprilriceSep 06, 2023Copper Contributor
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
- HansVogelaarSep 06, 2023MVP
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:
- In the Visual Basic Editor, click anywhere in the code, then select Run > Run Macro, or press F5.
- In Excel, press Alt+F8 to activate the Macros dialog. You should now see the name of the macro that you copied/pasted.
- 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.