How To Copy An Entire Row To Another Worksheet Based On Cell Value In Excel

We almost always use several spreadsheets in Excel, no matter what we're doing. A dashboard for reporting, a master worksheet for all the data, and finally numerous worksheets for different data kinds are the standard formats for each project. You may construct a project management application in which you aggregate all of the projects in a single spreadsheet. Assign and track projects for each employee on different spreadsheets. Let's assume I have 10 project information on my master worksheet and I want to give three projects to John and list them on a worksheet named john. For three projects, I can do it manually just fine but if I have 100 projects then doing it manually is time consuming and prone to error. But I can accomplish it with the help of VBA. It’s easy, accurate and fast.

I'll teach you how to copy a row if the cell contains a certain term in this tutorial. I'll use VBA, which will take a few minutes to set up and copy the full row.

Let's look at a simple situation and then begin the method to copy data in Excel if a condition is met. I'm using a simple example so you can simply adapt it to your own worksheet. Anyone who wishes to copy data from one sheet to another in Excel may do it by following the steps shown in this article.

The following is the scenario used in the video and within the code:

Sheet 1: This is the worksheet from which we will copy data. Replace this with the name of your worksheet. That's whatever it is.

Sheet 2: This is our final spreadsheet, where we'll paste our data. Inside the code, change this name to the name of the target worksheet.

Column Name: The third column contains the text from which we need to copy certain rows to a different sheet. Because the third column's name is C, we must include it in our code. Replace "C1:C" in the code with the name of the column you want to use. Let's assume your specific text is in column E, therefore you'll change "C1: C" in the code to "E1: E."

Text: "Done" is written in column C. I want to duplicate the full row to another (destination) sheet based on this text. You must replace it in the code with your own wording.

So that was the circumstance and the code-changing instructions. Now, to duplicate the row to another sheet depending on cell value, follow the steps below.

Step 1: To begin, go to the Developer tab.

Step 2: Select "Visual Basic" from the drop-down menu.

Step 3: Click on "Insert."

Step 4: Select “Module” from the drop-down menu.

Step 5: Compose the program (to obtain the code, Click HERE).

Step 6: Exit the VBE (Visual Basic Editor).

Step 7: Go to the "Developer" tab and choose the macro.

Step 8: Run the "CopyRowBasedOnCellValue" macro. If you build a button to execute the macro, you may also run it.

Done! Based on the precise text, your data should be moved from your master worksheet to the target worksheet.

When using this code, keep the following in mind:

1. If you want to use his code again in the future, save the workbook in the Excel Macro Enabled Workbook format, which is.xlsm.

2. When you access this worksheet in the future, you must click the "Enable Content" option. For security concerns, Excel disables macros, which is why you must click that button every time. If you wish, you may also turn it off. Check out this Macro Security lesson. It comes with extensive instructions that you can use to fine-tune macro security.

3. Only make the changes described in the scenario; otherwise, it will not function properly.

4. This code will paste the whole row onto the next available row on the target worksheet. If the destination worksheet has any formatting or data, it will paste the row to the next empty row.

5. Finally, this code will copy data each time it is called, resulting in duplicate values. If you don't want to do that, have a look at this tutorial: How to Move Rows to a New Worksheet and Delete Duplicates Using Text

So, if a cell includes a certain text, this is how you may quickly transfer a row to another sheet. If you want to move data instead of copying it checkout this tutorial: How to Move Rows to a Specific Worksheet Based on Text in Excel. l Thank you so much for taking the time to read this far. If you can, please consider being a patron. Thank you once again.