Automatically Record a Changing Excel Value Over Time in WPS Office Spreadsheets
Question details
The user needs to automatically log a workbook value that changes over time into the next unfilled row of another workbook instead of manually copying it at scheduled times.
- Product
- WPS Office Spreadsheets
- Device & OS
- not provided
- Scenario
- A portfolio value changes over time and must be captured and written to the next empty row of another workbook on a schedule.
- Observed behavior
- The current process requires manually copying the value at scheduled times.
Open both the source workbook and the destination log workbook in WPS Office Spreadsheets, and make sure the destination sheet has a clear column for the date/time and the value you want to record.
Create a WPS Spreadsheets Macro to Log the Value
Use this macro-based approach when you want hands-free logging of a changing value into the next empty row of another workbook.
WPS Office Spreadsheets supports VBA-compatible macros from the Developer tab. The macro reads the current value from a source cell, finds the next unfilled row in the destination workbook, and writes the timestamp and value automatically.
In WPS Spreadsheets, go to the Developer tab. If the Developer tab is not visible, click the Menu button, select Options, open Customize Ribbon, and enable Developer. Then click View Code or Macros to open the macro editor.
In the macro editor, insert a new module and create a macro that sets the source cell, copies its value, finds the last used row in the destination sheet with End(xlUp), and writes the current date and value to the next row.
Run the macro once from the Macro dialog or press F5 in the editor. Switch to the destination workbook and confirm that the value and timestamp appear in the next empty row.
Schedule Automatic Captures with Application.OnTime
Use this approach when the value must be recorded repeatedly at set intervals without manually running the macro each time.
Automate Value Logging with WPS Office Spreadsheets Macros
Use the Developer tab and macro editor in WPS Office Spreadsheets to capture a changing value and append it to the next empty row of another workbook. WPS Office supports VBA-compatible macros, so you can automate scheduled logging without leaving your existing Excel workflow.
- 1. Install WPS Office: Download WPS Office from the official website and install WPS Spreadsheets on your Windows or macOS computer.
- 2. Open the workbooks: Open the source workbook that contains the changing value and the destination workbook where the value should be logged.
- 3. Enable the Developer tab: In WPS Spreadsheets, click the Menu button, go to Options, open Customize Ribbon, and enable the Developer tab.
- 4. Create and schedule the macro: Open the macro editor from the Developer tab, insert the logging macro, and add an Application.OnTime call if you need scheduled captures.
- 5. Run and verify: Run the macro once, then check the destination workbook to confirm the value was written to the next unfilled row.
Frequently Asked Questions
Can WPS Office run Excel VBA logging macros?
Yes. WPS Office Spreadsheets has a Developer tab and a VBA-compatible macro editor, so basic logging actions such as reading a cell, finding the next empty row, and appending a value usually work without extensive changes.
How do I make the macro run automatically at scheduled times?
Add an Application.OnTime statement inside the macro to schedule the next run at a set interval. The workbook must remain open in WPS Spreadsheets, or you can use Windows Task Scheduler to open the macro-enabled workbook on a schedule.
Does the destination workbook need to be open?
It is easiest to keep the destination workbook open before running the macro. If it is closed, the macro can open it with a Workbooks.Open statement and then close it after writing the value.
How do I save the workbook so the macro is not lost?
Use File > Save As and choose Excel Macro-Enabled Workbook (*.xlsm). Saving as a regular .xlsx file can strip the macro from the workbook.
Why is the macro writing to the wrong row or leaving blank rows?
This usually happens when the destination sheet has formatting, hidden spaces, or stray data below the table, or when the macro uses the wrong column to find the last used row. Use a consistent column such as Date or Value for End(xlUp), and clear extra rows below the log range.
