Skip to content
Home » IF Excel Cell Contains Text Then Copy to Another Sheet | Copy Cell Based on Condition

IF Excel Cell Contains Text Then Copy to Another Sheet | Copy Cell Based on Condition

    In this tutorial I am going to explain how to copy records to another sheet based on a condition.  I explore three methods:

    1. Filter and then copy/paste – this method is easiest for beginners
    2. Use the FILTER function – this method won’t work in Excel versions prior to Excel 365
    3. Use a VBA macro – this method is the most automated

    5 FREE EXCEL TEMPLATES
    Plus Get 30% off any Purchase in the Simple Sheets Catalogue!

    Click here to download the featured file (including code).

    METHOD 1: Filter and then copy/paste

    Method 1 used the filtering capabilities in Excel.  To use this method:

    1. Select a cell within your data
    2. Use the shortcut CTRL SHIFT L to display the filter buttons
    3. Filter out any records that you don’t want to copy
    4. Select a cell within your data
    5. Use the shortcut CTRL A to select all the data
    6. Use the shortcut CTRL C to copy the data (this will copy visible records only)
    7. Navigate to the worksheet that you want to copy the records to
    8. Click in the cell that you want to paste your records into
    9. Use the shortcut CTRL V or ENTER to paste the copied records

    METHOD 2: Use the FILTER function

    Method 2 uses the FILTER function.  The FILTER function is not available in versions of Excel prior to Excel 365.  To use this method:

    1. In a new worksheet enter column headings (the same as the column headings in your data)
    2. In the cell directly underneath the first column heading, enter a formula using the FILTER function.
      The FILTER function has three arguments
      array is the array of values you want to filter (your original data set)
      include is a logical test that specifies what records to include
      [if_empty] allows you to specify what to return if no records meet your test – this is non-mandatory

    Example FILTER formula featured in the video: = FILTER(Data!A2:E14,Data!E2:E14=”Over budget”)

    METHOD 3: Use a VBA Macro

    Method 3 uses the VBA macro below:

    Sub CopyOverBudgetRecords()

Dim StatusCol As Range
Dim Status As Range
Dim PasteCell As Range

'You will need to adapt this code for your scenario - follow the STEPS below


'STEP1: Change the sheet name and range in the line of code below to match the sheet name
'and range holding the data that you want to copy rows from.
Set StatusCol = Sheet1.Range("E2:E14")

For Each Status In StatusCol

'STEP2: Change the sheet name and range in the lines of code below to match the sheet name
'and cell that you want to copy your data to. You only need to specify one cell -
'the first cell you will copy to.
    If Sheet5.Range("A2") = "" Then
        Set PasteCell = Sheet5.Range("A2")
    Else
'STEP3: In the line of code below, the range should refer to your first column heading
        Set PasteCell = Sheet5.Range("A1").End(xlDown).Offset(1, 0)
    End If
'STEP4: I have included three ways of copying the data. To use one of the methods, delete
'the apostrophe before the words IF Status at the beginning of the line.
'You can only use one of the options.  The third option is currently active.

'This one was used in the video, but will only work if your criteria is in column 5
'and you have five cells per record
    'If Status = "Over budget" Then Status.Offset(0, -4).Resize(1, 5).Copy PasteCell

'This one copies the entire row - right across the worksheet
    'If Status = "Over budget" Then Status.EntireRow.Copy PasteCell

'This one only copies the relevant cells, rather than the entire row and it doesn't
'matter which row contains the criteria or how many cells you need to copy.
'It won't work, however, if you have blank cells in your data.
    If Status = "Over budget" Then Range(Status.End(xlToLeft), Status.End(xlToRight)).Copy PasteCell
Next Status
        
End Sub

    To use this VBA macro:

    1. Open the Visual Basic Editor (VBE) using the shortcut ALT F11
    2. Click Insert | Module to create a module within the VBA project
    3. Paste the code I have provided into the code window of the new module
    4. Make changes to the code as explained in the video https://youtu.be/W4swMZe0TEE?t=237
      or follow the instructions included within the code
    5. Use the shortcut F5 to run the macro
    6. Use the shortcut ALT Q to close the VBE
    7. When you use VBA, you must save your workbook as Macro-Enabled