Excel VBA Copy Rows Matching a Project Code to Another Worksheet
Question details
Copy all Excel rows whose project code in column D matches a specified value into another worksheet by using VBA.
- Product
- Microsoft Excel VBA
- Device & OS
- not provided
- Scenario
- The user needs to filter source data by a project code, such as 123, and load all matching records into a destination sheet.
- Observed behavior
- The destination worksheet should contain every row with the requested project code after the macro runs.
Confirm that the project-code data begins in column D at row 8 and that the source and destination worksheet names exactly match the names used in the macro. Decide whether row 8 is a header row, because AutoFilter needs a header included in the filtered range.
Use AutoFilter to copy all rows with the matching project code
Use this method when the project code is in column D and you want a fast VBA solution that copies every visible filtered row to another worksheet.
Include the header row in the AutoFilter range, then copy only the visible cells after filtering. The macro below clears the destination sheet before placing the matching records there.
In Excel, press Alt+F11, select Insert > Module, and paste the macro into the new module.
Update the worksheet names in Set src = Worksheets("Paste into here") and Set dst = Worksheets("Load File"), then replace "123" in Criteria1 with the project code you want to copy.
Press F5 in the VBA editor, or go to Excel Developer > Macros, select Load_File, and click Run. Use this code: Sub Load_File() Dim src As Worksheet, dst As Worksheet Dim lastRow As Long Set src = Worksheets("Paste into here") Set dst = Worksheets("Load File") lastRow = src.Cells(src.Rows.Count, "D").End(xlUp).Row dst.Cells.ClearContents On Error Resume Next src.Range("A7:D" & lastRow).AutoFilter Field:=4, Criteria1:="123" src.Range("A7:D" & lastRow).SpecialCells(xlCellTypeVisible).Copy dst.Range("A1") On Error GoTo 0 If src.AutoFilterMode Then src.AutoFilterMode = False End Sub
Change "A7:D" in both range references to include all columns needed in the destination, for example "A7:H" & lastRow. Keep Field:=4 only when the project code remains the fourth column of that selected range.
Loop through source rows when you need more control over copied data
Use a row-by-row loop when you need to copy selected columns, transform values, or apply additional conditions beyond a single project-code filter.
Filter and Copy Project Code Records with WPS Spreadsheet
For a lightweight way to review and move project-code records without relying on an Excel VBA macro, use WPS Spreadsheet. Its familiar spreadsheet interface lets you filter column D, select the matching rows, and copy them into another worksheet with a workflow that is easy to migrate from Microsoft Excel.
Frequently Asked Questions
Why does my AutoFilter VBA macro copy the header row too?
AutoFilter requires a header row in the filtered range, so the visible header is copied with the matching data. If you do not want it in the destination, copy the header separately or change the copied range to start on the first data row after filtering.
How do I filter for a project code stored as a number instead of text?
Set Criteria1 to the matching value format used in column D. For numeric codes, use Criteria1:=123; for text codes or codes with leading zeros, use Criteria1:="00123" and format the source values consistently.
How can I copy all columns in each matching row instead of only columns A through D?
Expand the filtered range to the final required column, such as src.Range("A7:H" & lastRow). Keep the AutoFilter Field number aligned with column D's position within that range; it is Field:=4 when the range starts in column A.
What should the macro do when there are no rows with the requested project code?
Use error handling around SpecialCells(xlCellTypeVisible), because it can fail when no visible data rows exist. You can display MsgBox "No matching project code found" and clear the filter before ending the macro.
Why is Field:=1 in some AutoFilter examples and Field:=4 in others?
The Field number is relative to the first column of the filter range. If the range begins at D, project code column D is Field:=1. If the range begins at A and project codes are in D, it is Field:=4.
