You are currently viewing How to copy a row based on a cell value in Excel

How to copy a row based on a cell value in Excel

It is possible to copy a row based on a cell value while working with Microsoft Excel spreadsheets. Doing this saves you time that you would have otherwise spent scrolling through different worksheets looking for information.

The article below will show you different methods you can copy a row based on a cell value. Please read on to find out.

Method 1: copy rows if a column contains a specific value with the Filter command in Excel.

In this method, you will find out the cell (s) that contains the specific value in a specified column using the Filter command. Copy the filtered row in Excel. Here is what to do;

1. In an open excel workbook, select the column that you will filter.

2. On the main menu ribbon, click on the Data tab.

3. Under the Sort & Filter group, click on the 'Filter' option.

4. A drop-down arrow will be displayed on the right side of the selected column's first cell. Click on the arrow and select Number Filters to display a popup menu list.

5. In the list, select Custom Filter to display a dialog box.

6. Under the different fields provided, fill in your preferences and click OK in the open dialog box.

7. Please select all the filtered out rows and copy them.

8. Select a blank row or cell, and paste the filtered row.

 

Method 2: copy an entire row to another sheet based on cell value with VBA code.

1. Press the Alt + F11 keys simultaneously to open the Microsoft Visual Basic for Applications editor window. You can open this window by going to the Developers tab in your worksheet and clicking on the Visual Basic option.

2. In the VBA window, click on the Insert tab and select Module.

3. In the code window, copy and paste your macro code. It is the code that will enable you to copy and move the row. The code must contain the row you want to move, the destination worksheet you wish to locate the row, the column with a certain value, and the exact value you will move.

Sheets("sheet1").Range("C:E").Copy Sheets("sheet2").Range("G:I")

Replace the cell values to match with rows you want to copy

4. Press the F5 key to run your macro code. The row will be copied and moved to sheet 2.

 

Method 3: copy rows to another sheet based on specific cell values in Google sheet

To copy a row using this method, a formula is applied to achieve this.

In your new worksheet, select where you want to paste the specific rows, enter the following formula, and press Enter.

=filter(original!range, original!column=specific criteria) where;

Original! the Range is the sheet name and date range you want to copy your row (s). For example, assuming you want to copy data from column A to F, your formula will look like this original! A:F

Original!column=specified criteria mean to copy the rows that contain specific criteria in a certain column. For example, original! F: F= "Complete."

Your final formula will end up looking this way =filter(original!A:F, original:F= "Complete")

Conclusion

The article above has given out different ways of copying a row based on a cell value in excel. I hope you find it informative and helpful.

Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.