Use Excel VBA to Send Form Data to the Correct Monthly Worksheet in WPS Office
Question details
The user needs a VBA routine that reads the month value from cell A2 and the day value from cell A3, selects the matching monthly worksheet in WPS Spreadsheets, calculates the target row with an offset for the header row, and writes the form values.
- Product
- WPS Office Spreadsheets
- Device & OS
- not provided
- Scenario
- A data-entry form in a WPS Spreadsheets workbook uses cell A2 for the month value and cell A3 for the day value. The user wants VBA to route form data to the matching monthly worksheet and the row determined by the day value.
- Observed behavior
- The workbook does not yet automatically write the form values to the correct monthly worksheet, print the form, clear the input cells, and save the workbook.
Open the workbook in WPS Office Spreadsheets and enable the Developer tab through the ribbon or settings. Check that the month value in A2 exactly matches an existing monthly worksheet name and that row 1 is the header row used in the row offset.
Create a VBA macro to route form data to the monthly worksheet
Use this method when you need the form to automatically select the worksheet named by cell A2 and write to the row based on cell A3 plus the header offset.
The macro reads the month text directly from A2 as the worksheet name. It adds 1 to the day value in A3 so that day 1 lands in row 2 after the header row.
Write the form values to the columns you need before printing, clearing, and saving. Adjust the sheet name, cell ranges, and column letters to match your workbook.
In WPS Office Spreadsheets, click the Developer tab and then click View Code, or press Alt+F11 to open the VBA editor.
In the VBA editor, click Insert > Module to create a new code module.
Paste the following macro while the form sheet is active, then adjust the cell ranges and column letters to match your form: Dim w As Worksheet Dim r As Long Set w = Worksheets(Range("A2").Value) r = Range("A3").Value + 1 ' Write form values to the target row columns w.Cells(r, 1).Value = Range("B2").Value w.Cells(r, 2).Value = Range("B3").Value ' Print the form, clear the input cells, and save ActiveSheet.PrintOut Before:=True Range("A2:A3").ClearContents ThisWorkbook.Save
Click inside the macro and press F5, or assign the macro to a button from the Developer tab so you can run it while the form sheet is visible.
Verify worksheet names and row offsets before running the VBA macro
Use this preparation method to avoid subscript errors when the month in A2 does not match a worksheet name.
Send Excel Form Data to the Correct Monthly Worksheet with WPS Office VBA
Use the VBA editor built into WPS Office Spreadsheets to read the month and day cells, select the matching monthly worksheet, write form values to the correct row, print, clear, and save a macro-enabled workbook.
- 1. Open WPS Office Spreadsheets: Launch WPS Office, open your macro-enabled workbook, and make sure the Developer tab is enabled.
- 2. Open the VBA editor: Click Developer > View Code or press Alt+F11 to open the Microsoft Visual Basic for Applications editor.
- 3. Insert and paste the routing macro: In the editor, click Insert > Module, paste the VBA code that reads A2 as the worksheet name and A3 as the row offset, then save the macro.
- 4. Run the macro: Press F5 with the cursor inside the macro, or assign the macro to a button from the Developer tab and click it.
- 5. Verify and save the workbook: Confirm the form data appears in the correct monthly worksheet and save the workbook as an Excel Macro-Enabled Workbook.
Frequently Asked Questions
Why does VBA show “Subscript out of range” when I run the form macro?
Usually the month value in A2 does not exactly match a worksheet name, or the monthly worksheet does not exist. Check the worksheet tab names for spaces, capitalization, and missing months, then create or rename the worksheet as needed.
How do I make the data write below the last used row instead of using the day value?
Use r = Worksheets(month).Cells(Rows.Count, 1).End(xlUp).Row + 1 to find the next empty row based on the last used cell in a column, rather than using Range("A3").Value + 1.
Does WPS Office support Excel VBA macros?
Yes, WPS Office Spreadsheets can run VBA macros in supported macro-enabled workbook formats. Some advanced VBA objects may have limitations, so test the macro after opening or converting the workbook.
How do I assign the macro to a button?
In WPS Spreadsheets, add a shape or Form Button through the Developer tab, right-click it, choose Assign Macro, select the macro, and click OK.
How do I print the form and clear input cells from the same macro?
Use ActiveSheet.PrintOut Before:=True to print the form sheet, then clear the entry cells with a command such as Range("A2:A3").ClearContents, and save the workbook with ThisWorkbook.Save.
