Copy range from worksheet to worksheet

[Robandemmy]

Hello,

I am looking for a way to copy a cell range P:T from row 8:200 if populated on one worksheet named “OTV” and paste it in worksheet “Routings” starting in cell A1 while keeping the formatting of the “Routings” worksheet. There are also 6 other tabs that I would like to copy as well and paste sequentially after the “OTV” paste.

Is this possible?

Thanks, I appreciate any help!

 

[Fluff]

In the VB editor, place the cursor anywhere in the code & press F8, this will enable you to step through the code line by line.
Keep pressing F8 until you get an error. What line are you on when the error occurs & what is the error message.

 

[Robandemmy]

In the VB editor, place the cursor anywhere in the code & press F8, this will enable you to step through the code line by line.
Keep pressing F8 until you get an error. What line are you on when the error occurs & what is the error message.

Hi,

I'm good up until "With Sheets(Ary(i))" and after that I get a "Run-time error '9': Subscript out of range"

 

[Fluff]

In that case you need to check the sheet names. The names in the array need to match the sheet names. Check for any leading/trailing spaces.

 

[Robandemmy]

Ok, I did a copy paste to match up the names. I think there were some trailing spaces. I am getting an error 400 now when I run it.

 

[Robandemmy]

Ok, I did a copy paste to match up the names. I think there were some trailing spaces. I am getting an error 400 now when I run it.

Also, if I repeatedly hit F8 it will cycle through lines 7-11 10 times then a "Run-time error '1004': Appliocation -defined or object-defined error" occurs

 

[Fluff]

By my count it should be copying the data from "Finishing", but not "Confection" Is that right?
If so what is the last row in col P on that sheet?

 

[Robandemmy]

Hi,

I need all of the data from columns P, Q, R, S and T (ideally only P, Q, S ,T as R is a useless column that I cannot delete), rows 8:150 in the "269_NK", "Extrusion(Profiled)", "Extrusion(Profiled) CPX", "Calendering (Flat)", "Tringles", "Cutter", "Complexing", "Confection", "Finishing", "Curing_OGen", "OTV" tabs copied to "Routings tab starting in "A6"

Sorry for any confusion!!

 

[Fluff]

If it's copying the data from all the sheets, are you saying that it's working?

 

[Robandemmy]

No, it is not working. I get a 400 error when I try to run it.

In the VB editor I can cycle through the code by hitting F8 with no problem. Continuing to got F8 continues at the line “with sheets(ary(i))” through to “next i” where I get a runtime 1004 application defined error the 8th time I get to “end with”

 

[Fluff]

If you haven't changed the order of the sheet names in the array, that should mean that sheet "cutter" got copied, but sheet "complexing" didn't.
If that is the case what is the last row of data on sheet "complexing"?
Also do you have the code in a regular module, or is it in either a sheet module, or the ThisWorkbook module