Move Excel Rows Older Than Three Years Automatically with VBA in WPS Spreadsheets
Question details
The user needs a VBA macro to automatically move rows from Sheet3 to Sheet4 when the date in column G is more than three years before today.
- Product
- WPS Spreadsheets
- Device & OS
- not provided
- Scenario
- Managing aging records in a workbook by archiving rows based on a date cutoff.
- Observed behavior
- Rows with dates older than three years remain in Sheet3 and are not automatically moved to Sheet4.
Open the workbook in WPS Spreadsheets, enable macros or save the file as a macro-enabled workbook (.xlsm), and confirm that Sheet3 and Sheet4 exist with dates in column G.
Create a VBA Macro to Move Rows Older Than Three Years from Sheet3 to Sheet4
Use this method for a repeatable, automatic macro that evaluates column G against today's date and archives qualifying rows to Sheet4.
The macro calculates a dynamic cutoff with DateAdd("yyyy", -3, Date), then loops from the last used row in column G to the first data row. Processing from bottom to top prevents row skipping after deleting rows.
In WPS Spreadsheets, click the Developer tab and select View Code, or press Alt+F11 to open the VBA editor.
In the VBA editor, click Insert > Module to create a code module for the macro.
Add a macro that sets cutoff = DateAdd("yyyy", -3, Date), finds the last used row in Sheet3 column G, loops from bottom to top, copies each qualifying row to the next empty row in Sheet4, and deletes the source row.
Press F5 or click Run > Run Sub/UserForm in the VBA editor, then switch back to the workbook and verify that old rows were moved from Sheet3 to Sheet4.
Use AutoFilter to Manually Move Rows Older Than Three Years
Use this no-code method when macros are not enabled or you only need to move outdated rows once.
Move Excel Rows Older Than Three Years with WPS Spreadsheets VBA
WPS Office can run the same VBA macro in WPS Spreadsheets. Open your Excel workbook, enable macros, paste the DateAdd-based code into the VBA editor, and run it to move rows older than three years from Sheet3 to Sheet4.
- 1. Download WPS Office: Go to wps.com and click Download to install WPS Office on your Windows PC.
- 2. Open your Excel workbook: Launch WPS Spreadsheets, click Menu > Open, and open the .xlsm or .xlsx workbook that contains Sheet3 and Sheet4.
- 3. Enable macros: If prompted, enable macros or set macro security to allow VBA projects in WPS Spreadsheets.
- 4. Add the macro: Click the Developer tab > View Code or press Alt+F11, insert a module, and paste the macro that uses DateAdd to move old rows.
- 5. Run the macro: Press F5 in the VBA editor or click Run > Run Sub/UserForm, then return to the workbook and confirm the rows moved to Sheet4.
Frequently Asked Questions
Does this VBA macro work in WPS Spreadsheets if my file is currently .xlsx?
WPS Spreadsheets can run VBA macros, but you should save the workbook as a macro-enabled workbook (.xlsm) so the code is retained. A standard .xlsx file may not save macros.
How do I change the aging period from three years to a different number?
In the VBA code, change -3 in DateAdd("yyyy", -3, Date) to another negative number, such as -1 for one year or -5 for five years, to adjust the cutoff.
Why should the macro loop from the bottom of the sheet to the top?
Looping from bottom to top prevents skipped rows. When a row is deleted, the rows below shift up; processing upward avoids evaluating a moved row twice or missing the next row.
What if Sheet4 already contains data?
Your macro should find the next empty row in Sheet4, usually with End(xlUp).Row + 1, before copying each qualifying row. This preserves existing data and appends archived rows below it.
Can I run this macro automatically when opening the workbook?
Yes, after testing the macro manually, you can call it from a Workbook_Open event or assign it to a button in WPS Spreadsheets to run automatically.
