VBA & Macro Problems

Move Completed Rows from Multiple Excel Worksheets with VBA

Natalie TaylorNatalie Taylor Nov 18, 2026 869 views

Question details

Use a workbook-level VBA procedure to collect completed rows from multiple worksheets and record their source sheet names.

Move Completed Rows from Multiple Excel Worksheets with VBA
Product
Microsoft Excel
Device & OS
not provided
Scenario
A workbook contains action rows on several worksheets, with completion indicated by Done in column R.
Observed behavior
Rows marked Done should be added to the Complete PU Actions sheet, with the source worksheet name in column A and the original row data starting in column B.
Before you start

Create a worksheet named Complete PU Actions with headers in row 1, and confirm that source sheets use row 1 for headers and column R for status. Save the workbook as an Excel Macro-Enabled Workbook (.xlsm) and enable macros only if you trust the file.

Solution 1Recommended

Run a workbook-level VBA procedure to collect completed rows

Use this procedure to copy matching rows from every source worksheet while recording the source sheet name.

The procedure skips the destination sheet and searches column R for Done, ignoring capitalization and surrounding spaces. It copies rows without deleting them by default.

Destination column A must contain a source sheet name for every previously collected row. Source data is shifted one column to the right, so a source sheet with data in column XFD cannot be copied this way.

1
Open the workbook module

In Excel, open the workbook and press Alt+F11. In the VBA Project pane, expand the workbook and double-click ThisWorkbook.

2
Insert the procedure

Paste this code into the ThisWorkbook code window: Public Sub CollectCompletedRows() Dim ws As Worksheet, wsDest As Worksheet Dim lastCell As Range Dim lastRow As Long, lastCol As Long Dim r As Long, nextRow As Long Set wsDest = ThisWorkbook.Worksheets("Complete PU Actions") For Each ws In ThisWorkbook.Worksheets If Not (ws Is wsDest) Then Set lastCell = ws.Cells.Find(What:="*", LookIn:=xlFormulas, _ SearchOrder:=xlByColumns, SearchDirection:=xlPrevious) If Not lastCell Is Nothing Then lastCol = lastCell.Column If lastCol = ws.Columns.Count Then MsgBox "Cannot shift data from column XFD on " & ws.Name Exit Sub End If lastRow = ws.Cells(ws.Rows.Count, "R").End(xlUp).Row For r = lastRow To 2 Step -1 If Not IsError(ws.Cells(r, "R").Value) Then If LCase$(Trim$(CStr(ws.Cells(r, "R").Value))) = "done" Then nextRow = Application.Max(2, wsDest.Cells(wsDest.Rows.Count, "A").End(xlUp).Row + 1) wsDest.Cells(nextRow, "A").Value = ws.Name ws.Range(ws.Cells(r, 1), ws.Cells(r, lastCol)).Copy _ Destination:=wsDest.Cells(nextRow, "B") ' Uncomment the next line to move rather than copy: ' ws.Rows(r).Delete End If End If Next r End If End If Next ws End Sub

3
Run and check the results

Place the cursor inside CollectCompletedRows in the VBA editor and press F5. Open Complete PU Actions and check that column A contains sheet names and the copied data begins in column B.

4
Enable deletion only if needed

To move rows instead of copying them, remove the apostrophe before ws.Rows(r).Delete in the VBA editor, then run the procedure again. The bottom-up loop prevents deleted rows from causing later matches to be skipped.

Run a workbook-level VBA procedure to collect completed rows
Check before running again: Copy mode leaves source rows in place, so another run can add duplicates. Copied formulas may also adjust their references when moved to the destination.
Free Microsoft Office alternative

Review completed action rows in WPS Spreadsheets

WPS Office is a free, lightweight alternative with a familiar spreadsheet interface and support for Excel formats, making it easy to open and migrate workbook data. You can filter and copy completed rows manually; it does not directly fix or guarantee execution of this Excel VBA procedure.

Filter status columns and review completed actions in a spreadsheetOpen and edit Microsoft Excel XLSX filesWork in a familiar interface when moving from ExcelUse a free, lightweight office suite
microsoft office alternative - wps office

Frequently Asked Questions

Does the macro delete completed rows from their original sheets?

Not by default. To delete each row after copying it, remove the apostrophe before ws.Rows(r).Delete in the VBA editor. The procedure processes rows from the bottom up to avoid skipping matches.

Why are completed rows duplicated after I run the macro again?

In copy mode, the original Done rows remain on their source sheets, so the procedure will collect them again. Remove or change their status after checking the results, or enable the deletion line if you intend to move them.

Can I use a different destination sheet name?

Yes. Create or rename the destination worksheet, then change "Complete PU Actions" in the Worksheets reference in the VBA code to match its exact name.

Why does the destination data start in column B?

Column A stores the source worksheet name. The procedure copies the source row beginning at destination column B, shifting the original columns one position to the right.

How do I keep the macro when saving the workbook?

In Excel, choose File > Save As and select Excel Macro-Enabled Workbook (.xlsm). A standard .xlsx file cannot retain the VBA procedure.