Moving Complete Rows Based On Cell Value In Column D

[marwood]

Hi All,
I am trying to complete a parts ordering system for work with my limited excel knowledge.
I currently have 15 worksheets with various names & each sheet has 5 x columns: A=Item B=Description C=Cost D=Order E=Total.
What I need to happen is when a quantity is entered into Column D, that the complete row with the value added is automatically copied to the first worksheet & if further items ordered it goes to next row down.... I have attached a few pics of what my sheets look like...Is this possible?
Any help or feedback would be greatly appreciated...

https://i1249.photobucket.com/albums/hh502/marwood3/Order Sheet_zpstwvrsaov.png
http://i1249.photobucket.com/albums/hh502/marwood3/LTK60_zpsqozebbny.png
http://i1249.photobucket.com/albums/hh502/marwood3/W-Swivels_zpscboiybi9.png

​

 

[My Aswer Is This]

Posting this image does not help me. I need you to tell me in words what you want.

I never saw anything in your original post about deleting anything.

 

[marwood]

Re: Help Moving Complete Rows Based On Cell Value In Column D

I do not understand this:

when I delete the value from Column D the copied row remains?​

Your original post said:

What I need to happen is when a quantity is entered into Column D, that the complete row with the value added is automatically copied to the first worksheet & if further items ordered it goes to next row down....

So you want what deleted from where?

You want the row deleted from Sheet(1)??


​

So Sorry if I am not explaining myself correctly.
What I am trying to achieve is a excel ordering system for our customer which they send through on a weekly basis.
So When our customer orders a part in Column D it is automatically transferred to sheet 1 which you have achieved hands down with many thanks... When the customers order is completed all in Column D are cleared in readiness for next weeks order.
quantities in Column D are cleared to enable next weeks orders. But when I clear the values in Column D the copied Rows still remain on sheet 1 and do not clear? can this be done automatically also?

Hopefully I'm making sense & I am really appreciate the help and understanding...

 

[My Aswer Is This]

Re: Help Moving Complete Rows Based On Cell Value In Column D

Well this gets a lot more complicated. If you go into sheet 10 and clear all the values in column D how is the script suppose to know what rows in sheet(1) need to be deleted?

How can the script know what rows in sheet(1) came from Sheet(10)?

 

[marwood]

Re: Help Moving Complete Rows Based On Cell Value In Column D

Well this gets a lot more complicated. If you go into sheet 10 and clear all the values in column D how is the script suppose to know what rows in sheet(1) need to be deleted?

How can the script know what rows in sheet(1) came from Sheet(10)?

Good point! I have no idea LOL
I guess it would be easier for the customer just to clear the values manually for each future order...
I do really appreciate all you help and understanding...

 

[My Aswer Is This]

Re: Help Moving Complete Rows Based On Cell Value In Column D

This could be done if we did something like this:
When you activate the script I provided which is activated when you change a value in column D
The current sheet name would be put in column AA of the row before it is copied to Sheet(1)

Then when you want to clear all those rows from Sheet(1) You enter "Delete" in Range("D1")the script looks on Sheet(1) and Deletes all rows with that sheet name in column("AA")

If something like that would work let me know.

 

[marwood]

Re: Help Moving Complete Rows Based On Cell Value In Column D

This could be done if we did something like this:
When you activate the script I provided which is activated when you change a value in column D
The current sheet name would be put in column AA of the row before it is copied to Sheet(1)

Then when you want to clear all those rows from Sheet(1) You enter "Delete" in Range("D1")the script looks on Sheet(1) and Deletes all rows with that sheet name in column("AA")

If something like that would work let me know.

Cheers for reply.
I think it could work but, will that now require the customer to the manually enter Delete in column D?
What I have done for now is given the customer the excel sheet with no scripts assigned & when the customer sends us the completed order for processing we manually enter your script and the enter the qty again over there's so the script copies to sheet 1.
Is there a way that we enter the script you provided & it automatically searches in Column D and copies this data across if there is a value in column?

Hopefully this makes sense...

 

[My Aswer Is This]

Re: Help Moving Complete Rows Based On Cell Value In Column D

So if I understand things your now wanting the original script to run looking in column D for any value.
And if any value is in column D copy this row to sheet(1) is that what you want?

So it's not when you change a value in column D it's when you activate a script.

It that what you want?

Normally no script runs automatically. So in the previous script you had to change a value in column D

Now my previous script did not have to be added to every sheet. That's why it's called a Thisworkbook script.

The same script worked in all sheets in the Workbook. Now in this case I told the script to not work in sheet(1) because sheet(1) was the copy to sheet.

So this new script your now asking for has to be activated by something

It can be activated by you using a Shortcut Key like Ctrl+R

Or you double clicking on lets say Range("A1")

So please explain if this is how you want things now and how do you want to activate the script.

And we are now not talking about removing rows from sheet(1) but talking about copying rows to sheet(1)

 

[marwood]

Re: Help Moving Complete Rows Based On Cell Value In Column D

So if I understand things your now wanting the original script to run looking in column D for any value.
And if any value is in column D copy this row to sheet(1) is that what you want?

So it's not when you change a value in column D it's when you activate a script.

It that what you want?

Normally no script runs automatically. So in the previous script you had to change a value in column D

Now my previous script did not have to be added to every sheet. That's why it's called a Thisworkbook script.

The same script worked in all sheets in the Workbook. Now in this case I told the script to not work in sheet(1) because sheet(1) was the copy to sheet.

So this new script your now asking for has to be activated by something

It can be activated by you using a Shortcut Key like Ctrl+R

Or you double clicking on lets say Range("A1")

So please explain if this is how you want things now and how do you want to activate the script.

And we are now not talking about removing rows from sheet(1) but talking about copying rows to sheet(1)

I am so hopeless at trying to explain myself or word you my requirements, for which I apologise for...
Ok I will try to explain better

So every time a value is entered into Column D on any of the other 15 tabs, this row information is automatically sent to sheet (1).
If the customer decides to change the value already entered in Column D on any tab this value automatically changes the quantity in Sheet (1) that has already automatically been sent.
Then when the customer is ready to complete a new order they clear all the values in Column D which then deletes all information transferred to sheet (1) in readiness for new values to be transferred/copied.

I really hope I have explained this ok.

 

[My Aswer Is This]

Re: Help Moving Complete Rows Based On Cell Value In Column D

Well I'm probable very hard at understand things.

If this workbook is in your workbook on your computer or system how is the customer making the changes.

And doing what it now seems like you want is not something I believe I can help you with.

If sheet 12 column D has 20 entered you want this row copied to sheet(1).

But then if customer wants to change 20 to 30 you now want this row copied to sheet(1) and the previous row that was copied over deleted. But you said there is nothing on this row that would identify what sheet it came from.

I would think if each sheet is for a different customer there would be a customer number some place on that row which would identify what sheet it came from

When you look on sheet(1) row(25) how do you know what customer ordered this item?

 

[marwood]

Re: Help Moving Complete Rows Based On Cell Value In Column D

Well I'm probable very hard at understand things.

If this workbook is in your workbook on your computer or system how is the customer making the changes.

And doing what it now seems like you want is not something I believe I can help you with.

If sheet 12 column D has 20 entered you want this row copied to sheet(1).

But then if customer wants to change 20 to 30 you now want this row copied to sheet(1) and the previous row that was copied over deleted. But you said there is nothing on this row that would identify what sheet it came from.

I would think if each sheet is for a different customer there would be a customer number some place on that row which would identify what sheet it came from

When you look on sheet(1) row(25) how do you know what customer ordered this item?

Sorry this workbook will be given to the customer to use, then once they have entered all the quantities they want to order the sheet is then emailed to me with all the ordered quantities sent to sheet (1).
Having this information sent to sheet (1) makes it easier for me to see what they have ordered instead of going through each tab to find values in Column D.