Move Entire Row to Different Sheet

**jhamernik** · 05-22-2012, 02:49 PM

I have a spreadsheet which contains very specific information on different cases that I'm currently working on. In Column E, "Status", there is a formula which will allow the cell to change from "OPEN" to "CLOSED" when information is entered into Column I, "HRG DATE".

There are two components to what I need:

If Column E = "CLOSED", AND the information in Column I is a date value, I would like the entire row cut/pasted to Sheet2, starting at A3, with each new row being added in the next available row.

If the value in Column I is NOT a date, (and the status is "CLOSED"), then I'd like the row moved to Sheet3 starting, also, in A3.

I'd like the macro to run everytime their is a change made in Column E - but as I have A LOT of data already in the sheet, I need it to scan what is currently entered, as well.

I've looked through many many posts, and so far can't come up with anything that really works for me.

((Unfortunately, because the data is confidential I can't supply a copy of my worksheet, but if needed I can supply something similar with fake information.))

**arlu1201** · 05-22-2012, 03:00 PM

I would suggest that you do the movement of your rows on the click of a button, instead of having it triggered automatically. This will help you maintain your data properly and will ensure smooth movement of your data between sheets.

**jhamernik** · 05-22-2012, 03:25 PM

Okay... I'm new to all of this, so how would I do that? Or is there a forum I can read to do this?

**arlu1201** · 05-23-2012, 09:26 AM

Other than a Date, what other values can be present in column I?

**jhamernik** · 05-23-2012, 04:25 PM

Thanks for the reply!

Just the word "none" or I can leav it blank, whichever is easiest is the way I'll do it.

**arlu1201** · 05-24-2012, 03:51 AM

It will be good if column I contains either a date or blank. Do not put anything else. You can use this macro.

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 View | Macros
Select a macro in the list, and click the Run button.

**jhamernik** · 05-30-2012, 01:19 PM

This works great! I So appreciate all of your efforts! I Can't thank you enough!

**daillest319** · 05-30-2012, 01:49 PM

Is it possbile to alter this code for the following... If Column O=Bad Number move to sheet1 or If Column O=Good Number move to sheet 2 etc.

Basically Column O has data that need to be moved to the correct worksheet.

**arlu1201** · 05-30-2012, 02:36 PM

Daillest319,

Your post does not comply with Rule 2 of our Forum RULES. Don't post a question in the thread of another member -- start your own thread. If you feel it's particularly relevant, provide a link to the other thread. It makes sense to have a new thread for your question because a thread with numerous replies can be off putting & difficult to pick out relevant replies.