WPS Spreadsheet VBA: Move New Dates Right and Clear the Input Cell
Question details
The user needs to automatically move a date entered in column B to the next available column on the same row, then clear column B for the next entry.
- Product
- WPS Spreadsheet VBA macros
- Device & OS
- not provided
- Scenario
- A worksheet receives new dates in column B and should build a horizontal record across the same row without requiring manual copying.
- Observed behavior
- The desired worksheet change event moves the entered value to the next available column on its row and clears the original input cell.
Open the target workbook and identify the worksheet where dates will be entered in column B. Confirm that macro execution is enabled and prepare to save the file in the macro-enabled .xlsm format.
Add a worksheet change event to move dates from column B
Use this solution when each new value entered in column B should move to the next available column on the same row automatically.
The event must be placed in the specific worksheet module so it responds to edits on that sheet. Events are temporarily disabled while the value is moved and cleared, preventing the macro from triggering itself repeatedly.
In WPS Spreadsheet, open the workbook, press Alt+F11, and locate the target worksheet under the workbook in the Project Explorer.
Double-click the relevant worksheet, such as Sheet1, in the Project Explorer. Do not paste the event into Insert > Module or another standard module.
Paste this code into the worksheet code window: Private Sub Worksheet_Change(ByVal Target As Range) If Target.CountLarge > 1 Then Exit Sub If Target.Column = 2 And Target.Value <> "" Then Application.EnableEvents = False Cells(Target.Row, Cells(Target.Row, Columns.Count).End(xlToLeft).Column + 1).Value = Target.Value Target.ClearContents Application.EnableEvents = True End If End Sub
Close the VBA editor, choose File > Save As, select the Excel Macro-Enabled Workbook (*.xlsm) format, and enable macros when prompted. Enter a date in column B and verify that it moves right on the same row while the input cell is cleared.
Automate Date Tracking with WPS Spreadsheet VBA
WPS Spreadsheet lets you place a worksheet change event in the correct sheet module, automate the horizontal date-entry workflow, and save the workbook as a macro-enabled file for repeated use.
- 1. Open the workbook in WPS Spreadsheet: Launch WPS Office, select Spreadsheet, and open the workbook that contains the date-entry sheet.
- 2. Access the worksheet VBA module: Press Alt+F11, select the target worksheet in the Project Explorer, and open its code window.
- 3. Paste the Worksheet_Change event: Insert the event code that detects column B, writes the value to the next available column, and clears the original cell.
- 4. Save as an XLSM file: Use File > Save As and choose Excel Macro-Enabled Workbook (*.xlsm), then enable macros and test a date entry in column B.
Frequently Asked Questions
Where should the Worksheet_Change code be pasted in WPS Spreadsheet?
Press Alt+F11, find the target sheet under the workbook in Project Explorer, and double-click that worksheet. Paste the code in its worksheet module, not under Insert > Module.
Why does the macro only respond to values entered in column B?
The condition Target.Column = 2 restricts the event to column B. Change the number if the input column is different; for example, column C is column 3.
What happens if the row already contains values to the right?
The macro checks the last used cell in that row with End(xlToLeft) and writes the new value in the next column, preserving the existing entries.
Why must events be disabled while the macro runs?
Writing a value and clearing the input cell are worksheet changes. Application.EnableEvents = False prevents those actions from calling Worksheet_Change again and creating a recursive loop.
Which file format is required to keep this VBA macro?
Save the workbook as Excel Macro-Enabled Workbook (*.xlsm). If you save it as a regular .xlsx file, the VBA project may not be retained, and macros must be enabled when the workbook opens.
