How to Copy Cell If Condition Is Met in Excel (7 Easy Ways)

The sample dataset contains Product, Salesman, and Net Sales information. There are 2 types of products available. We’ll show how to copy the rows based on the cells inside them.

Method 1 – Apply the Filter Feature to Copy a Cell If Condition Is Met in Excel

Steps:

Click on cell C4, which is the Product header.
Go to Home, select Sort & Filter, and choose Filter.

Click the drop-down icon beside Product.
Check the box for AC as we want rows with AC.
Press OK.

This’ll return the required rows.
Select and copy the range by pressing Ctrl + C.
You can paste the values wherever you want by pressing Ctrl + V.

Method 2 – Copy Cell by Using the FILTER Function

Steps:

Select cell B12.
Insert this formula:

=FILTER(B5:D10,C5:C10=C5)

Press Enter.
This will spill the rows that have cells with desired conditions.
We can perform this task where we want to paste, or we can copy the following results and paste them there.

Method 3 – Insert the IF Function for Copying Cell with Condition=

Steps:

Click on cell B12.
Insert the formula:

=IF($C5="AC",B5,"")

Hit Enter.
Apply AutoFill to complete the column.
You can copy and paste the values where you want.

Method 4 – Copy a Cell by Using the Find Command in Excel

Steps:

Select the cell range B5:D10.
Press Ctr + F to open the Find and Replace dialog box.
Type AC in Find what and click Find All.

Select all the cells.

Close the dialog box.
You’ll see the cells are selected in the dataset.
You can copy the desired cells.

Method 5 – Input the SWITCH Function to Copy a Cell If the Condition Is Met

Steps:

Choose cell B12.
Insert the formula:

=SWITCH(TRUE,$D5>10000,B5,"")

Click Enter.
Use AutoFill to fill the series.
This formula checks if the D column values are greater than 10,000. It’ll return blank for the rows where the D column cells do not meet the condition.
You can input this formula where you want to paste the data.

Method 6 – Combine CHOOSE and MATCH Functions for Copying Cells

Steps:

Select cell B12.
Insert the following formula:

=CHOOSE(MATCH($C5,$C$5:$C$10,0),B5,"")

Press Enter and apply AutoFill.
The MATCH function looks for AC and returns the cell location. The CHOOSE function gives out the B5 cell value as this row contains AC.
You can now copy the cells that satisfy the conditions.

Method 7 – Run Excel VBA to Copy Cells If the Condition Is Met

Steps:

Select Developer and choose Visual Basic.

The VBA window will appear.
Click on Insert and select Module.
Copy the below code and paste it there.

Sub Solution_For_Loop()
Input_Sheet = "VBA"
Input_Range = "B5:D10"
Criteria_Column = 3 'Net Sales is in column 3 of the range B5:D10.
Output_Sheet = "Paste"
Output_Cell = "B2" 'The output range will start from cell B2.
Set Rng1 = Worksheets(Input_Sheet).Range(Input_Range)
Set Rng2 = Worksheets(Output_Sheet).Range(Output_Cell)
Count = 1
For i = 1 To Rng1.Rows.Count
    If Rng1.Cells(i, Criteria_Column) > 10000 Then
        For j = 1 To Rng1.Columns.Count
            Rng2.Cells(Count, j) = Rng1.Cells(i, j)
        Next j
        Count = Count + 1
    End If
Next i
End Sub

Save the file and press F5 to run the code.
This will return the rows with net sales greater than 10,000 in the Paste sheet.
See the following picture for the outcome.