Macros need whole line moved to new worksheet automatically

[aboyal]

I want to create a macro that when date is inserted into certain cell, have the whole line moved to a new worksheet, and re move from original sheet.<?xml:namespace prefix = o ns = "urn:schemas-microsoft-comfficeffice" /><o></o>
<o> </o>
attempted to create the formula, but insufficient excel knowledge,<o></o>
<o> </o>
<o> </o>
needs whole line moved to new worksheet if column "i" contains<o></o>
<o> </o>
-------------------------<o></o>
needs a macro that will<o></o>
1. check the completed date cell for data<o></o>
2. IF the data is present, then COPY the data in the row from one sheet to another<o></o>
3. THEN delete the data from the original sheet<o></o>
4. LOOP through and go to the next cell to check<o></o>
5. Variables would have to be used<o></o>
<o> </o>
<o>Any guidence would be super appriciated </o>
<o>Thank you in advance! </o>

 

[Joe4]

Welcome to the Board!

Can you post the code that you have tried so far?

Variables would have to be used<?xml:namespace prefix = o ns = "urn:schemas-microsoft-comfficeffice" /><o></o>

Why? They could be used but it may not be necessary, so I am just curious as to why you say they would have to be used?

 

[aboyal]

I havent created a formula myself I have no idea where to start with this one.

number 1 to 5 was provided by a friend I had asked but they did not have the knowledge to make the formula.

to keep things as simple as possible
Lets say I have an excell sheet named (Active jobs)
Columns A to H
& Column E has a completion date
as i enter a completion date into Column E
is it possible for the whole line to move to another excell sheet named (Completed jobs)
Same number of Columns A to H?
with out using copy and paste function?

Thanks for your help

 

[Joe4]

with out using copy and paste function?

Why?

Is this a homework assignment?

 

[aboyal]

Its for my work to make it eisier for myself & some of the other staff so when we're adding jobs on the road using just a laptop. not a homework assignment been out of college since 2002.

 

[Joe4]

Then why the aversion to cut & paste (which can be used in macros) and saying variables have to be used?

It just seems rather odd that you say you have no idea how to do this, but yet have some very strong ideas/requirements on how it should be done.

 

[aboyal]

Well to be quite honest I work for a City organization in the Roads department I would like to use this to make it more efficient for our team. The It team were the people I went to to find a solution and they told me that they cannot allocate their time to work on the macros needed but they provided me with the details (1-5) as to how it should be done & referred me to check on MrExcel.com. below is the original email i was returned & that is why iam here, If i could do this type of work i wouldnt be on MrExcel.com. This is out of my expertise a different skill set then mine.

Any help you can provide would be great!


email from Bonnie one of our Service Desk team members
In REQ000000305387 you requested :
<?xml:namespace prefix = o ns = "urn:schemas-microsoft-comfficeffice" /><o> </o>
Client wants to create macro that when date is inserted into certain cell and then have the whole line moved to a new worksheet, and re move from original sheet.<o></o>
<o> </o>
attempted to create the formula, but insufficient excel knowledge,<o></o>
<o> </o>
attaching sheet to ticket,<o></o>
<o> </o>
needs whole line moved to new worksheet if column "i" contains<o></o>
<o> </o>
-------------------------<o></o>
<o> </o>
as per our KB, we are not equipped for the time and research required to devise lengthy macros. <o></o>
Your request needs a macro that will<o></o>
1. check the completed date cell for data<o></o>
2. IF the data is present, then COPY the data in the row from one sheet to another<o></o>
3. THEN delete the data from the original sheet<o></o>
4. LOOP through and go to the next cell to check<o></o>
5. Variables would have to be used<o></o>
<o> </o>
Here is a link that is one of the best for assistance in creating EXCEL macros<o></o>
<o> </o>
http://www.mrexcel.com/
<o> </o>
I have used it for years in the past when I was able to spend the time creating macros
regards, <o></o>
Bonnie McBride <o></o>
EHD Admin

 

[Joe4]

Ah, so your just going based on their proposal, but it doesn't sound like you are married to any of their "suggestions?. So you wouldn't be opposed if we didn't a way different from their suggestion, as long as it accomplishes what you want in the end? Is that right?

If so, I may have some time to look at it tomorrow.

 

[aboyal]

You nailed it Joe4 Yes not opposed to doing it differently as long as it accomplishes the goal. if you could provide a solution to this, you will be the hero around here! like Yoda or should we say Joeda

Is there anything I can do for your time? Would def buy you a beer if Im ever passing thru what City you from?

 

[Joe4]

OK, just a few more questions to make sure I create something that works for you.

Based on what you said here:

to keep things as simple as possible
Lets say I have an excell sheet named (Active jobs)
Columns A to H
& Column E has a completion date
as i enter a completion date into Column E
is it possible for the whole line to move to another excell sheet named (Completed jobs)
Same number of Columns A to H?
with out using copy and paste function?

Do you want this to work as you are entering data in column E (immediately upon entering data in this column), or do you want a macro to run against data already entered into column E?

When the rows of data is removed from your original sheet, do you want the blank row deleted so that all other rows move up one?

When the data is pasted into your "Completed jobs" sheet, do you want to start in row 1, and then put each succeeding row after that?