Pull Data From One Worksheet to Another Automatically Based on Cell Criteria In Excel -

Step 1: Overview

The macro CopyRecordsBasedOnCriteria helps filter data based on multiple criteria and copy the results to a new worksheet. The VBA code is provided on this page, so you can copy it and add it to your workbook. You can download the featured file here. Watch the video above to see how the macro works and how to install it.

Step 2: Adding the Macro to Your Workbook

Open the workbook where you want to use the macro.
Press Alt + F11 to open the VBA Editor.
In the Project Explorer, locate your workbook (e.g., VBAProject (YourWorkbookName.xlsm)).
Save your workbook as a Macro-Enabled Workbook (.xlsm) if it isn’t already.
Select your workbook name in the Project Explorer.
Go to the menu and click Insert > Module to add a new module.
Copy the VBA code from this page and paste it into the module window (Ctrl + V).
Save your workbook to retain the macro.

Sub CopyRecordsBasedOnCriteria()
    Dim wsSource As Worksheet
    Dim wsTarget As Worksheet
    Dim filterRange As Range
    Dim dictCriteria As Object
    Dim i As Integer
    Dim colNumber As Variant
    Dim criteria As Variant
    Dim newSheetName As String
    Dim criteriaArray() As String
    Dim Key As Variant
    Dim andCriteria As Boolean

    On Error GoTo ErrHandler

    ' Initialize variables
    Set wsSource = ActiveSheet
    
    ' Dynamically determine the filter range based on the CurrentRegion of the active cell
    On Error Resume Next
    Set filterRange = ActiveCell.CurrentRegion
    On Error GoTo ErrHandler

    ' Check if a valid range is found
    If filterRange Is Nothing Or filterRange.Rows.Count = 1 Or filterRange.Columns.Count = 1 Then
        MsgBox "No data found here. Please select a cell within a your data range and try again.", vbExclamation
        Exit Sub
    End If

    Set dictCriteria = CreateObject("Scripting.Dictionary")
    
    ' Prompt user for up to 25 criteria
    For i = 1 To 25
        colNumber = Application.InputBox("Enter the column number for criterion " & i & " (or click Cancel to finish):", "Column Selection", Type:=1)
        If colNumber = False Then Exit For ' Cancel button was clicked
        If Not IsNumeric(colNumber) Or colNumber < 1 Or colNumber > filterRange.Columns.Count Then
            MsgBox "Invalid column number. Please enter a valid number between 1 and " & filterRange.Columns.Count & ".", vbExclamation
            i = i - 1
        Else
            criteria = Application.InputBox("Enter the criterion for column " & colNumber & ":" & vbCrLf & _
                                            "(Use operators like >, <, <> for numbers/dates; or enter text criteria directly)" & vbCrLf & _
                                            "(Use commas for OR criteria, or 'and' for AND criteria between values)", "Criteria Input", Type:=2)
            If criteria = False Then Exit For ' Cancel button was clicked

            ' Split the criteria into an array based on delimiters (comma or 'and')
            If InStr(criteria, "and") > 0 Then
                ' Split by "and" for AND criteria
                criteriaArray = Split(criteria, "and")
                andCriteria = True
            Else
                ' Split by comma for OR criteria
                criteriaArray = Split(criteria, ",")
                andCriteria = False
            End If

            dictCriteria.Add colNumber, Array(criteriaArray, andCriteria)
        End If
    Next i

    ' Exit if no criteria were provided
    If dictCriteria.Count = 0 Then
        MsgBox "No criteria were provided. Exiting macro.", vbExclamation
        Exit Sub
    End If

    ' Create a new sheet for results
    newSheetName = "Filtered Records"
    On Error Resume Next
    Set wsTarget = ThisWorkbook.Sheets(newSheetName)
    If Not wsTarget Is Nothing Then wsTarget.Delete
    On Error GoTo ErrHandler
    Set wsTarget = ThisWorkbook.Sheets.Add
    wsTarget.Name = newSheetName

    ' Apply filtering
    filterRange.AutoFilter
    For Each Key In dictCriteria.Keys
        colNumber = Key
        criteriaArray = dictCriteria(Key)(0)
        andCriteria = dictCriteria(Key)(1)

        If andCriteria Then
            ' Handle AND criteria by checking if it's between two values
            If UBound(criteriaArray) = 1 Then
                ' We have two criteria, so apply a 'BETWEEN' filter
                wsSource.Rows(1).AutoFilter Field:=colNumber, Criteria1:=">=" & Trim(criteriaArray(0)), Operator:=xlAnd, Criteria2:="<=" & Trim(criteriaArray(1))
            End If
        Else
            ' Handle OR criteria by applying multiple options
            If UBound(criteriaArray) > 0 Then
                ' Use AutoFilter for multiple OR conditions
                wsSource.Rows(1).AutoFilter Field:=colNumber, Criteria1:=criteriaArray, Operator:=xlFilterValues
            Else
                ' Single condition for OR logic
                wsSource.Rows(1).AutoFilter Field:=colNumber, Criteria1:=criteriaArray(0)
            End If
        End If
    Next Key

    ' Copy visible rows (including headers) to target sheet
    On Error Resume Next
    filterRange.SpecialCells(xlCellTypeVisible).Copy Destination:=wsTarget.Cells(1, 1)
    On Error GoTo ErrHandler

    ' Autofit columns in the target sheet
    wsTarget.Columns.AutoFit

    ' Clear filters
    wsSource.AutoFilterMode = False

    MsgBox "Records copied successfully to '" & newSheetName & "'.", vbInformation

Exit Sub

ErrHandler:
    MsgBox "An error occurred: " & Err.Description, vbCritical
    On Error Resume Next
    wsSource.AutoFilterMode = False
End Sub

Step 3: Adding a Macro Button to the Ribbon

In Excel, right-click on any existing Ribbon tab (e.g., Home, Insert) and select Customize the Ribbon.
In the Excel Options dialog:
- On the right-hand side, select the tab where you want to add the button.
- Click New Group to create a group within the tab, then select it.
On the left-hand side under “Choose commands from,” select Macros.
Find your macro (e.g., CopyRecordsBasedOnCriteria) and click Add to move it to the new group.
(Optional) Click Rename to change the button’s name or icon.
Click OK to save the changes.

Step 4: Adding a Macro Button to the Worksheet

Go to the worksheet where you want to add the button.
On the Ribbon, click Developer > Insert in the Controls group.
Under Form Controls, click the Button (Form Control) icon.
Click and drag on the worksheet to draw the button.
When the Assign Macro dialog appears:
- Select your macro (e.g., CopyRecordsBasedOnCriteria) and click OK.
Right-click the button, select Edit Text, and type a descriptive label (e.g., “Run Macro”).
Resize or reposition the button as needed.

Step 5: Assigning a Shortcut Key

Press Alt + F8 in Excel to open the Macro dialog.
Select your macro and click Options.
In the Macro Options dialog:
- Enter a shortcut key (e.g., Ctrl + Shift + R).
- (Optional) Add a description for the macro.
Click OK to assign the shortcut.

You can now run the macro by pressing the assigned shortcut key.

Step 6: Testing and Saving the Macro

Test the macro to ensure it works as intended.
Save your workbook as a Macro-Enabled Workbook (.xlsm) to retain the macro and all settings.

Optional: Saving the Macro Globally

To use the macro across multiple files, save it to your Personal Macro Workbook. Follow the same steps to paste the macro into the VBAProject (PERSONAL.XLSB) and save the Personal Macro Workbook.

Overview of the Macro

The CopyRecordsBasedOnCriteria macro filters data from a selected range based on up to 25 user-defined criteria and copies the filtered results to a new worksheet named Filtered Records.

It supports:

OR criteria: Specify multiple values for a column, and any record matching any of the values will be included.
AND criteria: Define conditions that must all be true simultaneously for a record to be included (e.g., “between two values”).
Flexible use of operators like >, <, =, <>, and more for numeric or date comparisons.

How the Macro Works

Selecting the Data: The macro dynamically determines the data range based on the CurrentRegion of the active cell (the area surrounding your data). Ensure your data has headers and consistent formatting.
Prompting for Criteria: The macro asks you to:
- Enter the column number where the condition applies.
- Enter the criteria for filtering that column.
Filtering the Data:
- The macro applies the specified criteria to the data.
- Rows meeting the criteria are copied (along with headers) to the new worksheet.
Output: The filtered records are pasted into a new sheet named Filtered Records, with columns autofitted for clarity.

Specifying Criteria

Entering OR Criteria

To specify multiple values for a column, separate them with commas.
- Example: For a “Region” column, enter:
  North, South, East
  This will filter rows where the region is North, South, or East.

Entering AND Criteria

Use the word and to specify conditions that must both be true.
- Example: For a “Sales” column, enter:
  100 and 300
  This will filter rows where sales are between 100 and 300.

Using Operators

You can enter operators to specify numeric or date comparisons:
- >100: Greater than 100.
- <500: Less than 500.
- <>Completed: Not equal to “Completed”.
- >=01/01/2024 and <=12/31/2024: Between two dates.