How to move entire row to another sheet based on cell value in Excel?

 

Move Entire Row To Another Sheet Based On Cell Value

Recently, we got to complete some accounts tasks in my office. Account data are entered into excel main sheet. Now the problem is to send some rows to other sheets based on the data in the cell. Then I started searching all over the internet but I found everywhere VBA code. I got fed up with VBA code because I’m not a coding geek. I want to keep it as simple as possible. Then finally I found macros. You can create macros in two ways

1. VBA code

2. By Actions.

I have chosen the second option “By Action” because my accounts task is not that much complicated. Let’s see how to move entire row to another sheet based cell value in excel using macros.

1. Open your excel sheet in which you like to move all entire row data of the main sheet to this sheet based on cell data.

2. Check whether you have Developer menu Tab Enabled

  • If not right click on ribbon and select customize ribbon

Excel Ribbon

  • Customize Ribbon >>> Main Tab >>> Developer – Check it and click OK

Developer option in Ribbon

  • Now you will able to see a Developer Tab
  • Keep other sheet header row same as the main sheet

3. Now In other sheet, Developer >>> Insert >>> Form Button(Under Form Controls)

Form Button

4. Draw a button on the sheet wherever you want and click OK

5. Double click on button and change the button label(name) as you like

6. Right click on Button >>> Assign Macro. Once you click on assign macro option, you see a macro popup like this.

Assign Macros to excel button

7. Then click on “Record”. You macro will start recording your excel actions.

Record Macro in Excel

 

8. Now select Main Sheet. I named the main sheet as “Master” and the other sheet as “Benjamin”

9. In Master sheet, the first column has some cell data as Benjamin and I like to move all those “Benjamin” rows to “Benjamin” Sheet.

move rows to another sheet

 

10. To do that, after macro started recording, select all cells by pressing shortcut key ctrl+A or click on select all button before column A.

Select All Button in Excel

11. After that, Data >>> Filter. You will see drop-down arrow on all your column headers.

Filter Option in Excel

12. Click on Column A filter(the column which has the cell data that you need to copy – Benjamin) and uncheck all column cell data. Select which you need to copy.

Select from Drop Down list

 

13. Now your sheet will display only the rows which have the specified data in Column A.

14. Now copy the full sheet Ctrl + C and goto to “Benjamin” Sheet. Click on the first cell and paste it.

15. Unselect all then remove filter Data >>> Filter

16. Now at the bottom left corner of the sheet, you will see stop button. Click on it. That’s it. You have assigned actions for macros.

 

Move Entire Row To Another Sheet - Stop Macro Recording

Whenever I input some data in Master sheet, to update the Benjamin sheet I will be clicking on the Update button. I will be getting all the corresponding data on Benjamin sheet.

This is the simple method to move an entire row To another sheet based on cell value in Excel without coding. You can use macros for different types operation and make your tasks simple.

You may also like: 7 Excel Tips That Will Impress Your Boss