Create Excel Worksheet Tabs for Each Workday of a Month with WPS Spreadsheets
Question details
The user needs to automate adding worksheet tabs for every workday in a selected month in WPS Spreadsheets instead of creating and renaming tabs manually.
- Product
- WPS Spreadsheets
- Device & OS
- not provided
- Scenario
- Create a monthly workbook where each workday receives its own worksheet tab from a single selected date.
- Observed behavior
- Sheet tabs are currently created or renamed by hand; a VBA macro is needed to generate the workday tabs automatically.
Make sure the VBA macro component is available in WPS Spreadsheets: press Alt+F11 to confirm the VBA editor opens. If it does not, enable the Developer tab or install the VBA component through WPS Office settings before running the macro.
Run a reusable VBA macro to add workday worksheet tabs
Use this method when you want WPS Spreadsheets to generate an entire monthly workbook from one selected date and reuse the macro across files.
This macro asks for any date in the target month, calculates the workdays with Application.WorkDay, creates a new workbook, and adds one worksheet for each workday. It skips weekends automatically.
Sub WorkdayTabs() Dim dtm As Date, mnt As Long Dim wbk As Workbook, wsh As Worksheet On Error GoTo ErrHandler dtm = InputBox("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 = Format(dtm, "yyyy-mm-dd") Loop Application.DisplayAlerts = False wbk.Worksheets(1).Delete Application.DisplayAlerts = True Application.ScreenUpdating = True Exit Sub ErrHandler: Beep End Sub
In WPS Spreadsheets, press Alt+F11. If the editor does not open, click the Developer tab and choose Visual Basic.
In the VBA editor, click Insert > Module to create a new module where the macro will be stored.
Copy the macro code from the solution above into the module window, then click Save in the VBA editor.
Click inside the procedure, press F5 or choose Run > Run Sub/UserForm, enter any date in the target month, and click OK. WPS Spreadsheets creates a workbook with one workday tab per sheet.
To make the macro available in every workbook, save it in the Personal Macro Workbook (PERSONAL.XLSB) from the VBA editor before saving.
Create Workday Sheet Tabs Automatically with WPS Spreadsheets
Run the same Excel VBA macro in WPS Spreadsheets to create a workbook with one worksheet tab for every workday in a selected month. WPS Spreadsheets supports Excel workbooks and macros, so you can automate the task without manually renaming tabs.
- 1. Open WPS Spreadsheets and enable the Developer tab: Launch WPS Spreadsheets, click Menu > Options > Customize Ribbon, check Developer, and click OK.
- 2. Open the VBA editor: Click Developer > Visual Basic or press Alt+F11 to open the WPS VBA editor.
- 3. Insert and save the macro: Click Insert > Module, paste the workday macro, and save the file as an Excel Macro-Enabled Workbook (.xlsm) or in PERSONAL.XLSB for reuse.
- 4. Run the macro: Press F5 or click Run > Run Sub/UserForm, enter any date in the selected month, and let WPS Spreadsheets create all workday tabs.
Frequently Asked Questions
Will the macro include weekends when creating worksheet tabs?
No. Application.WorkDay skips Saturdays and Sundays by default. If you also need to skip holidays, supply a range of holiday dates to the WorkDay method.
Can I change how the worksheet tab dates are formatted?
Yes. In the line wsh.Name = Format(dtm, "yyyy-mm-dd"), change the format string to "mmm-dd-yyyy", "yyyy-mm-dd", or another valid sheet name format.
How do I run this macro in every WPS Spreadsheets workbook?
Save the macro in the Personal Macro Workbook (PERSONAL.XLSB). WPS Spreadsheets loads this file at startup, making the macro available in all workbooks.
What should I do if Alt+F11 does not open the VBA editor in WPS Office?
Enable the Developer tab through Menu > Options > Customize Ribbon, or install the VBA macro component from WPS Office settings. A current WPS Office version with macro support is required.
Does the macro delete the default blank worksheet?
Yes. After adding all workday tabs, the macro deletes the initial blank worksheet. If you want to keep it for notes, remove or comment out the wbk.Worksheets(1).Delete line.
