Copy Row to Sheet 2 if condition 'A' met, Copy Row to Sheet 3 if condition 'B' met etc....

[alip_123]

I am an excel beginner so need help.

I have 8 columns in Sheet 1 and hundreds of rows. In 'H' I have one of three different options 'Adam', 'Bob' and 'Chris'. I would like the entire row copied to Sheet 2 if Adam is chosen, entire row copied to Sheet 3 if Bob is chosen and Sheet 4 for Chris.

There are lots of rows of data to transfer across, so naturally I don't want gaps between rows in sheet 2,3,4 etc.

I don't wish to use an Auto Sort or Filter.

Is there an IF formula that will do this or a Macro?

I have no idea how to do these so if someone can help please do so step by step.

Thanks.

[arlu1201]

Do you want the copy paste to happen on the click of a button?

Should the rows be copied or moved?

What is the name of the sheet containing all the data?

[alip_123]

I just want it done automatically for example:

data in columns A-G. Column H will have one of four different options input i.e. Adam, Bob, Chris, Dan.

I then want all data collected in Sheet 2 every time Adam is input including all data from A-H

All data from Bob in Sheet 3 A-H etc etc.

Does that make sense?

[arlu1201]

Should the rows be copied or moved?

What is the name of the sheet containing all the data?

Are there sheet names titled "Dan", "Adam", etc?

Also, do you want to check if the sheet exists and if no, to create the sheet for you?

[alip_123]

The rows need to be copied as they still need to be on the main first sheet 'sheet1' then each other sheet is still currently called 'sheet2' 'sheet3' at the moment but could be changed. At the moment excel just has the first 3 sheets there that come when you start a new document.

THanks

[arlu1201]

What if today you select the dropdown for the 1st row as Adam and the macro copies the data to the Adam sheet or Sheet2. Then tomorrow, you change the value in the same cell (H2 as example) to Dan. Should the row be removed from Adam sheet and put in Dan sheet?

[alip_123]

I'm sorry I'm a relative office novice so don't understand that at all,

Can you tell me exactly what should be typed in a Macro or is there an IF formula. Something like IF cell contains 'ADAM' copy row 3 to Sheet 2???

[arlu1201]

Try this code

Please Login or Register  to view this content.

Copy the Excel VBA code
Select the workbook in which you want to store the Excel VBA code
Hold the Alt key, and press the F11 key, to open the Visual Basic Editor
Choose Insert | Module
Where the cursor is flashing, choose Edit | Paste

To run the Excel VBA code:
Choose Tools | Macro | Macros
Select a macro in the list, and click the Run button

Some pointers:

Instead of copying the data each time the dropdown is changed, its better to have it on a click of a button.
This macro will clear all sheets (except sheet1) excluding the headers. Then it will populate the data from row 2 of sheet1 going downwards. If the sheet does not exist, the macro will create it for you with the headers.