Hi all, I hope you can help me.
I have 2 worksheets: LineRegister and OrderSummary.
The LineRegister sheet has details of all orders line by line in sequence. Each order can have any number of lines but the number of columns never changes.
e.g. (it has many more columns but just to show you)
[TABLE="width: 500"]
<TBODY>[TR]
[TD]Order Number
[/TD]
[TD]Order Line Number
[/TD]
[TD]Dispatch Quantity
[/TD]
[TD]Dispatch Date
[/TD]
[/TR]
[TR]
[TD]6
[/TD]
[TD]1
[/TD]
[TD]1
[/TD]
[TD]12/02/13
[/TD]
[/TR]
[TR]
[TD]6
[/TD]
[TD]2
[/TD]
[TD]2
[/TD]
[TD]12/02/13
[/TD]
[/TR]
[TR]
[TD]6
[/TD]
[TD]3
[/TD]
[TD]1
[/TD]
[TD]12/02/13
[/TD]
[/TR]
[TR]
[TD]7
[/TD]
[TD]1
[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]7
[/TD]
[TD]2
[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]7
[/TD]
[TD]3
[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]7
[/TD]
[TD]4
[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]8
[/TD]
[TD]1
[/TD]
[TD]1
[/TD]
[TD]13/02/13
[/TD]
[/TR]
</TBODY>[/TABLE]
The OrderSummary (is like the above but has only the information for 1 order displayed at each time) allows the user to pull up the relevant information about a particular order but I also want them to be able to input the dispatch data and use a macro to copy and paste this to the relevant lines for the order in the LineRegister. The problem is that the order can have 3 rows or 10 rows or 100 but I've managed to work out the row start and end points from the Order Number column so I know the range to paste (it will vary for each order though so must be able to reference a cell detailing the actual range). Next I need to lookup that order in the LineRegister and then copy and paste this dispatch information into the relevant cells.
So, for the above table, the user would bring up the details for just order 7 in the OrderSummary and now wants to dispatch the products. They would key in the quantities and dates. I then want this to go to the relevant cells (in the above example: C5 to D8 - it cannot copy paste onto the entire columns as this would wipe any dispatch info for any other order). I have found a way to automatically find the start and end rows of an order so is there any way to use this to do what I need it to?
Sorry if it's a bit wordy or I haven't made sense!
Thanks, Jemma x
I have 2 worksheets: LineRegister and OrderSummary.
The LineRegister sheet has details of all orders line by line in sequence. Each order can have any number of lines but the number of columns never changes.
e.g. (it has many more columns but just to show you)
[TABLE="width: 500"]
<TBODY>[TR]
[TD]Order Number
[/TD]
[TD]Order Line Number
[/TD]
[TD]Dispatch Quantity
[/TD]
[TD]Dispatch Date
[/TD]
[/TR]
[TR]
[TD]6
[/TD]
[TD]1
[/TD]
[TD]1
[/TD]
[TD]12/02/13
[/TD]
[/TR]
[TR]
[TD]6
[/TD]
[TD]2
[/TD]
[TD]2
[/TD]
[TD]12/02/13
[/TD]
[/TR]
[TR]
[TD]6
[/TD]
[TD]3
[/TD]
[TD]1
[/TD]
[TD]12/02/13
[/TD]
[/TR]
[TR]
[TD]7
[/TD]
[TD]1
[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]7
[/TD]
[TD]2
[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]7
[/TD]
[TD]3
[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]7
[/TD]
[TD]4
[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]8
[/TD]
[TD]1
[/TD]
[TD]1
[/TD]
[TD]13/02/13
[/TD]
[/TR]
</TBODY>[/TABLE]
The OrderSummary (is like the above but has only the information for 1 order displayed at each time) allows the user to pull up the relevant information about a particular order but I also want them to be able to input the dispatch data and use a macro to copy and paste this to the relevant lines for the order in the LineRegister. The problem is that the order can have 3 rows or 10 rows or 100 but I've managed to work out the row start and end points from the Order Number column so I know the range to paste (it will vary for each order though so must be able to reference a cell detailing the actual range). Next I need to lookup that order in the LineRegister and then copy and paste this dispatch information into the relevant cells.
So, for the above table, the user would bring up the details for just order 7 in the OrderSummary and now wants to dispatch the products. They would key in the quantities and dates. I then want this to go to the relevant cells (in the above example: C5 to D8 - it cannot copy paste onto the entire columns as this would wipe any dispatch info for any other order). I have found a way to automatically find the start and end rows of an order so is there any way to use this to do what I need it to?
Sorry if it's a bit wordy or I haven't made sense!
Thanks, Jemma x
