Move Completed Rows from Multiple Excel Worksheets with VBA
Question details
Use a workbook-level VBA procedure to collect completed rows from multiple worksheets and record their source sheet names.

- 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.
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.
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.
In Excel, open the workbook and press Alt+F11. In the VBA Project pane, expand the workbook and double-click ThisWorkbook.
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
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.
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.

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.

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.




