Macro to Copy Data from Another Workbook Based on Criteria

Copying data between workbooks based on specific criteria is a common task in Excel, especially when dealing with large datasets. Doing this manually can be time-consuming and prone to errors. By using a VBA macro, you can automate the process, ensuring that only the rows meeting your conditions are transferred to the destination workbook.

With a macro, you can filter data dynamically based on single or multiple conditions, copy entire rows, and place them in another workbook without altering the original dataset. This not only saves time but also maintains consistency across your files, making your workflow much more efficient.

Sub CopyRowsBasedOnCriteria()
    Dim wsSource As Worksheet
    Dim wsDest As Worksheet
    Dim lastRowSource As Long
    Dim lastRowDest As Long
    Dim i As Long
    ' Define source and destination worksheets
    Set wsSource = Workbooks("Source.xlsm").Sheets("Sheet1")
    Set wsDest = Workbooks("Destination.xlsx").Sheets("Sheet1")
    ' Find the last used rows
    lastRowSource = wsSource.Cells(wsSource.Rows.Count, "A").End(xlUp).Row
    lastRowDest = wsDest.Cells(wsDest.Rows.Count, "A").End(xlUp).Row + 1
    ' Loop through source rows and copy if condition is met
    For i = 2 To lastRowSource ' start from row 2 to skip headers
        If wsSource.Cells(i, 1).Value = "North" Then
            wsSource.Rows(i).Copy Destination:=wsDest.Rows(lastRowDest)
            lastRowDest = lastRowDest + 1
        End If
    Next i
End Sub

This method allows you to copy rows from a source workbook into a destination workbook only if a specific column meets a given condition. For example, if column A contains the value “North”, all rows matching that condition will be transferred. This is useful when you want to extract only relevant data based on a single filter.

These are the datasets we will be using to demonstrate our methods.

The first dataset is located in the source workbook called Source.xlsm.

The second dataset is located in the destination workbook called Destination.xlsx.

Using our dataset, if we copy all rows where the Region = “North”, then rows for Alice, Ella, and Irene will be copied to the destination workbook.

Steps:

➤ Press  Alt  +  F11  to open the VBA Editor.
➤ Go to Insert >> Module to add a new module.

➤ Paste the following code:

Sub CopyRowsBasedOnCriteria()
    Dim wsSource As Worksheet
    Dim wsDest As Worksheet
    Dim lastRowSource As Long
    Dim lastRowDest As Long
    Dim i As Long
    ' Define source and destination worksheets
    Set wsSource = Workbooks("Source.xlsx").Sheets("Sheet1")
    Set wsDest = Workbooks("Destination.xlsx").Sheets("Sheet1")
    ' Find the last used rows
    lastRowSource = wsSource.Cells(wsSource.Rows.Count, "A").End(xlUp).Row
    lastRowDest = wsDest.Cells(wsDest.Rows.Count, "A").End(xlUp).Row + 1
    ' Loop through source rows and copy if condition is met
    For i = 2 To lastRowSource ' start from row 2 to skip headers
        If wsSource.Cells(i, 1).Value = "North" Then
            wsSource.Rows(i).Copy Destination:=wsDest.Rows(lastRowDest)
            lastRowDest = lastRowDest + 1
        End If
    Next i
End Sub

➤ Return to Excel, press  Alt  +  F8  , select CopyRowsBasedOnCriteria, and click Run.

➤ Suppose the source data has regions North, South, East, West.
➤ After running the macro, only rows with Region = “North” will appear in the destination workbook.

This method copies rows from a source workbook into a destination workbook only if they meet more than one condition. For example, you might want to copy rows where the Region = “South” and the Product = “Tablet”. This is particularly useful when filtering data with multiple requirements.

Using our dataset, if we copy all rows where Region = “South” and Product = “Tablet”, then only the rows for Bob (South, Tablet) will be transferred to the destination workbook.

Steps:

➤ Press  Alt  +  F11  to open the VBA Editor.
➤ Go to Insert >> Module to add a new module.
➤ Paste the following code:

Sub CopyRowsSouthTablet()
    Dim wbSource As Workbook, wbDest As Workbook
    Dim wsSource As Worksheet, wsDest As Worksheet
    Dim lastRowSource As Long, lastRowDest As Long
    Dim i As Long, copiedCount As Long
    ' Set your workbooks (must be open)
    Set wbSource = Workbooks("Source.xlsx")       ' Change to your source workbook name
    Set wbDest = Workbooks("Destination.xlsx")    ' Change to your destination workbook name
    Set wsSource = wbSource.Sheets("Sheet1")
    Set wsDest = wbDest.Sheets("Sheet1")
    ' Find last used rows
    lastRowSource = wsSource.Cells(wsSource.Rows.Count, "A").End(xlUp).Row
    lastRowDest = wsDest.Cells(wsDest.Rows.Count, "A").End(xlUp).Row
    ' Copy header if destination is empty
    If lastRowDest = 1 And Application.CountA(wsDest.Rows(1)) = 0 Then
        wsSource.Rows(1).Copy
        wsDest.Rows(1).PasteSpecial xlPasteValues
        lastRowDest = 1
    End If
    lastRowDest = lastRowDest + 1
    copiedCount = 0
    ' Loop through source data starting from row 2 (skip header)
    For i = 2 To lastRowSource
        If wsSource.Cells(i, "A").Value = "South" And wsSource.Cells(i, "D").Value = "Tablet" Then
            wsSource.Rows(i).Copy
            wsDest.Rows(lastRowDest).PasteSpecial xlPasteValues
            lastRowDest = lastRowDest + 1
            copiedCount = copiedCount + 1
        End If
    Next i
    Application.CutCopyMode = False
    MsgBox "Done! " & copiedCount & " row(s) copied.", vbInformation
End Sub

➤ Return to Excel, press  Alt  +  F8  , select CopyRowsSouthTablet, and click Run.

➤ Suppose the dataset contains multiple regions and products.
➤ After running the macro, only rows where Region = “South” and Product = “Tablet” (Bob’s entries) will appear in the destination workbook.

This method uses Excel’s Advanced Filter feature through VBA to copy rows that meet complex criteria, such as multiple conditions or logical operators (e.g., OR/AND). It’s more flexible than looping through rows and works well when criteria are stored in a separate range.

Using our dataset, if we create a criteria range where Region = “North” and Product = “Laptop”, then the Advanced Filter will copy only Alice’s rows (North, Laptop) into the destination workbook.

Steps:

➤ In the source workbook, create a criteria range (e.g., in F1:F2):
➤ Press  Alt  +  F11  to open the VBA Editor.
➤ Go to Insert >> Module to add a new module.
➤ Paste this code:

Sub CopyDataUsingAdvancedFilter()
    Dim wsSource As Worksheet
    Dim wsDest As Worksheet
    ' Define source and destination worksheets
    Set wsSource = Workbooks("Source.xlsx").Sheets("Sheet1")
    Set wsDest = Workbooks("Destination.xlsx").Sheets("Sheet1")
    ' Apply Advanced Filter
    wsSource.Range("A1:E100").AdvancedFilter _
        Action:=xlFilterCopy, _
        CriteriaRange:=wsSource.Range("F1:G2"), _
        CopyToRange:=wsDest.Range("A1"), _
        Unique:=False
End Sub

➤ Return to Excel, press  Alt  +  F8  , select CopyDataUsingAdvancedFiltert, and click Run.

➤ Suppose the criteria entered are: Region = North and Product = Laptop.
➤ After running the macro, only Alice’s Laptop sales from the North region will be copied to the destination workbook.

Frequently Asked Questions

Can I copy data based on partial matches (e.g., text contains “Lap” instead of “Laptop”)?

Yes, you can. In your criteria range, use wildcards like *Lap* to capture partial matches when using the Advanced Filter.

How do I copy data to a new workbook instead of an existing one?

Simply replace the destination workbook reference with:

Workbooks.Add
ActiveWorkbook.Sheets(1).Range(“A1”).PasteSpecial

Or programmatically set a new workbook as the destination.

Can I copy multiple sheets’ data at once?

Yes, but you’ll need to loop through each sheet in the source workbook and apply the same macro logic for each worksheet.

What if my dataset size changes frequently?

Instead of hardcoding the range (e.g., “A1:E100“), use UsedRange or find the last row dynamically:

lastRow = wsSource.Cells(wsSource.Rows.Count, “A”).End(xlUp).Row
wsSource.Range(“A1:E” & lastRow).AdvancedFilter …

Can I copy only values (without formatting or formulas)?

Yes. After copying, you can add:

wsDest.PasteSpecial xlPasteValues

to ensure only the values are transferred.

Wrapping Up

Copying data from one workbook to another based on criteria is a common task in Excel automation, and VBA makes it much faster and more efficient. In this guide, we explored three useful approaches: copying rows based on a single criterion, applying multiple conditions to filter the data, and leveraging the Advanced Filter method for more complex scenarios. Each method gives you flexibility depending on how simple or advanced your filtering needs are. By applying these techniques, you can eliminate repetitive manual work, ensure accurate data transfers, and streamline your reporting process across multiple workbooks.