copy row values to respective sheet if a criteria is met

is there a VBA code that will accomplish the following requirement:

Whenever a number is entered in sheet1 ColumnA the VBA checks to see if the number is either 1 or 2 or 3. if the number is 1 then copy all the contents in the row and place them in the next available row in sheet2. if the number is 2 then copy all the contents in the row and place them in the next available row in sheet3. if the number is 3 then copy all the contents in the row and place them in the next available row in sheet4. Also, if i edit "any" of the values in sheet1 that were already copied to sheet2, or sheet3, sheet4, i want the edit to be reflected in the respective sheet. for example, if i edited the "last" value from sheet1 columnA, like change 1 to a 2, i want the last values(row) copied to sheet2 deleted and the correst values entered in sheet3.

Thanks in advance for your assistance.

it doesn't have to be VBA, "but" i don't know of any excel formula that can "copy" cells, and place then in the next available row inanother sheet that already has data in it. i want the contents of the "row" to be copied. Bear in mind a row will have several cells with data in it, like cell A1, cell B1, cell C1 etc...

This may help with the first part of your request. Not sure how to handle the second part, though.

Please Login or Register  to view this content.

thanks for replying JOHN H.DAVIS, your VBA code works fine as is, but:

If i first enter 1 in the cell and then enter data in the adjacent cells, i notice the data in the adjacent cells are not copied to the respective sheet. i would have to first input data in the adjacent cells first then in columnA. Is there a way to fix that? like a VBA that would show an update button 3 cells away from the active row i'm typing in.

The code is based on the number you place in Column 1 ("A"). That's the event you requested in Post #1. I'm sure we can help, but I'm a bit confused on what you actually want. Do you want to include other Columns? If so then what would trigger the copy to respective sheets for the other columns?

I apologise for the misunderstanding, is it possible to get a VBA code that would show an update "button" each ttime i input data in a row and but would still check to see if the value in Column 1 ("A") is 1 or 2 or 3, and after button click enter the amount into respective sheets. the button would disappear after the update is completed to the relevant sheet, and reappear when i enter new data in another row in columnA.