How to Move Completed Rows in Excel VBA Without Changing Formula Results
Question details
The user needs a VBA macro to move rows from a source worksheet to a destination worksheet once marked as completed, ensuring that formula results convert to static values so they do not break upon moving.
- Product
- Excel
- Device & OS
- not provided
- Scenario
- Managing a project tracker where completed tasks are archived to a different sheet using automation.
- Observed behavior
- Standard copy-pasting or basic macro row moves cause formulas to recalculate or break due to changing cell references in the destination sheet.
Always save a backup copy of your workbook before running or testing new VBA macros, as macro actions cannot be undone using the standard Undo feature.
Convert Formulas to Values and Loop Bottom-to-Top
Modify your VBA script to convert specific formula cells to static values before copying the row, and iterate backwards through the rows to safely delete the originals.
When copying rows with formulas to a new sheet, relative references shift and can result in #REF! errors. Converting formulas to values beforehand ensures the data integrity is preserved in the archive sheet.
Additionally, when deleting rows based on a condition, looping from top to bottom causes Excel to skip rows. Looping from bottom to top prevents this issue.
Press ALT + F11 to open the Visual Basic for Applications Editor, and insert a new Module or locate your existing script.
Create a For loop that counts backwards. For example: 'For r = LastRow To 2 Step -1'. This ensures row deletions do not affect the index of upcoming rows.
Before copying the row, convert the target formula cells to static values by setting the value to itself: 'wsSrc.Cells(r, "D").Value = wsSrc.Cells(r, "D").Value'.
Copy the row to your 'Complete' sheet destination using explicit worksheet variables (e.g., wsDest), then delete the source row using 'wsSrc.Rows(r).Delete'. Avoid using 'Select' or 'Activate'.
Use Paste Special (Values Only) Method
Instead of converting the source data permanently, copy the entire row and use Paste Special to paste only the values into the destination worksheet.
Run VBA Macros Flawlessly with WPS Spreadsheet
WPS Office provides robust support for VBA macros in its advanced editions, allowing you to seamlessly execute, edit, and manage your Excel scripts. Archive completed rows and automate workflows without having to rewrite your existing code.
- 1. Install WPS Office: Download and install WPS Office, ensuring you are using the version that includes VBA support.
- 2. Open Your Macro Workbook: Launch WPS Spreadsheet and open your .xlsm or .xlsb macro-enabled workbook.
- 3. Access the VBA Editor: Navigate to the 'Developer' tab and click 'Macros', or press ALT + F11 to open the built-in VBA Editor.
- 4. Run Your Script: Execute your row-moving macro directly to archive your completed records with all formula values perfectly preserved.
Frequently Asked Questions
Why do formulas return #REF! when moved via VBA?
When a row containing relative formula references is moved to another sheet, the spreadsheet application tries to adjust those references based on the new location. If the referenced cells do not exist in the same relative position on the new sheet, it breaks and returns a #REF! error. Converting formulas to values prevents this.
Why is my VBA macro skipping rows when deleting them?
If you loop from top to bottom (e.g., Row 1 to 100), deleting Row 2 shifts Row 3 up to become the new Row 2. The loop then moves on to Row 3, which completely skips the original Row 3. Looping backwards (bottom to top using Step -1) prevents this indexing issue.
How do I disable screen updating in VBA to prevent flickering?
Add the line 'Application.ScreenUpdating = False' at the very beginning of your macro script. Remember to add 'Application.ScreenUpdating = True' at the end of the script to restore normal visual updates. This significantly speeds up the macro's execution time.
Why should I avoid Select and Activate in Excel VBA?
Using Select and Activate slows down your macro significantly because it forces the application to render graphical updates and change user focus. Using explicit worksheet and range references (like ws.Range("A1").Value) is much faster and less prone to runtime errors.
