VBA code to copy and past rows of data

[tompee29]

Hi guys,

hope fully I can explain this so you understand.....

I have a spreadsheet which manages raw material ordering for a factory. There is over 300 SKU's in the sheet and i am looking for a way to automate copying and pasting data from one row to the next for each SKU.
I have created a simplified version and attached it.
Basically for each item I want the code go though each SKU and copy the suggestion line and past values into the requested line
so:
E8:V8 copy, E9:V9 paste values
E21:V21 copy, E22:V22 paste values
E35:V35 copy, E36:V36 paste values

I appreciate you can do the brut force approach and just type this into the code (i.e. specifying which rows and columns to copy and paste) but due to the amount of SKU's in the spreadsheet and the number of tabs they are over it would be difficult to set up and maintain.
I was wondering if there was a smart way to set a macro up to do this - as in it cycles through a range of data looking for a trigger maybe in each sku which would cause it to copy and paste the data?!

Thoughts?
Thanks in advance,
Tom.

[davesexcel]

You can look for the "Requested" row and then do something.

Please Login or Register  to view this content.

[tompee29]

You can look for the "Requested" row and then do something.

Please Login or Register  to view this content.

Hi, thanks for that it worked a treat.
If I want this to work over a few worksheets how would the code look then (if we assume the are worksheet A,B and C)?

Many thanks,
Tom

[davesexcel]

Looping through the sheets.

Please Login or Register  to view this content.

[tompee29]

brilliant thanks.
Last question hopefully....
If I wanted to make the range dynamic i.e. so it isn't Row E to Row V everytime, what is the best way to do this? Can you specifiy the columns in a 2 cells on the sheet somewhere I.e. A1 and A2? Or could it be done by a question asked in a text box when the macro runs?
Many thanks,
Tom.

[davesexcel]

Maybe finding the last column in the sheet will do it.

Please Login or Register  to view this content.

[tompee29]

thank you, i'll give it a go!