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

In this advanced Excel tutorial, you’ll learn how to copy data from one sheet to another based on cell value in Excel using VBA. It is easy and takes only two minutes.

No matter what we do in Excel, we usually use several worksheets. The usual format of any project is a dashboard for reporting, a master worksheet for all the data, and lastly, several worksheets for different data types. Suppose you are creating a billing application on Excel, then you need to create a master worksheet for all transactions and a month-wise sheet for data from each month. Or you can create a project management application and in the master worksheet you aggregate all the projects. Assign and keep projects on separate worksheets for each employee. There are literally thousands of scenarios and each has one thing in common. You need to transfer data from one sheet to another.

Now let's say on my master worksheet I have 10 project details and I want to assign 3 projects to John, and I have a dedicated worksheet named John. How would you copy the data from the master sheet to John? Copy each row from the master and then paste it to John? What if you need to transfer 150 rows to another worksheet? Doing this manually is prone to error. You can do it using VBA. You can even create a button to run the vba code.

In this Advanced Excel Tutorial, I’ll show you an excellent way to copy a row if the cell contains a specific word. I’ll be using a VBA to solve this and it will take a few minutes to set it up and copy the entire row.

Now let's consider a basic scenario and then start the procedure to copy data if a condition is met in excel. I am using a basic scenario so that you can change it easily and match your own worksheet and situation. If anyone wants to copy data from one sheet to another in Excel, he/she can do it if they follow the procedure from this tutorial.

The scenario used in the video and inside the code:

Sheet 1: This is our source worksheet and we will copy data from here. Change this name to your worksheet name. Whatever that is.

Sheet 2: This is our destination worksheet and we will paste the data here. Change this name to your destination worksheet name inside the code.

Column Name: The third column is the column where we have the text based on which we need to copy specific rows to another sheet. As the third column name is C, we need to specify that inside our code. Change "C1:C" in the code into your preferred column name. Let’s say you have your specific text in column D, so you’ll be changing "C1: C" into "D1: D" in the code.

Specific Text: In column C we have written "Done". This is the text based on which I want to copy the entire row to another (destination) sheet. You need to change that in the code into your specific text.

So, this was the scenario and instructions to change the code. Now let's follow the procedure below to copy the row to another sheet based on cell value.

Step 1: Go to the Developer tab.

Step 2: Click on "Visual Basic”.

Step 3: Select “Insert”.

Step 4: Click on “Module”

Step 5: Write the code (to obtain the code, click on the link below).

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

Step 7: Click on the macro under the “Developer” tab.

Step 8: Run the macro named "CopyRowBasedOnCellValue". You can also run the macro if you create a button to run the macro.

Done! Your data should be transferred from your master worksheet to the destination worksheet based on the specific text.

Things to remember when using this code:

1. You must save the workbook in Excel Macro Enabled Workbook format, which is.xlsm, if you wish to use his code again in the future.

2. You must click on the "Enable Content" button when you open this workbook in the future. Excel basically disables macros for security reasons, which is why you need to click on that button every time. You can also stop this if you want. Check out this tutorial on Macro Security. It has detailed instruction and you can use that to tweak macro security.

3. Only change the things I explained in the scenario, otherwise it won’t work.

4. This code will copy the entire row to the destination worksheet and paste it into the next available row. So, if you have any formatting or any data on the destination worksheet, it will paste the row to the next empty row.

5. Lastly, this code will copy data every time you run the code, leaving duplicate values. If you don’t wish to do that, checkout this tutorial instead: How to Copy Rows to Another Worksheet and Remove Duplicates Based on Text

So, this is how you can easily copy a row to another sheet if the cell contains a specific text. Thanks a million for reading this far. Please support us by becoming a patron if possible. Leave a comment if you have any questions. Thanks again.

follow the MS Excel tutorial


You may also like:

How to Automatically Move Rows to a Specific Worksheet Based on Text in Microsoft Excel

In this tutorial, you’ll be amazed to see how easy it is to move data to different worksheets based on condition. It is quick, easy and everyone can do it. I’ll be using vba to transfer data from sheet to sheet and the code is also given so that you can use it in your project.......