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]

Hi & welcome to MrExcel.
Does this do what you want for the OTV sheet?

Sub copyData()
   Dim Lr As Long
   With Sheets("OTV")
      Lr = .Range("P" & Rows.Count).End(xlUp).Row
      Sheets("Routings").Range("A1:E" & Lr - 7).Value = .Range("P8:T" & Lr).Value
   End With
End Sub

For the other sheets, do you just have the 8 sheets in your workbook?
If you have more, what are the names of the 6 other sheets?

 

[Robandemmy]

Thank you for the response!

I have 30 tabs but only need the data from 11 of the tabs. The start of the data string is the same cell (P8) on each worksheet.

Also, I have an edit...I need to paste starting in cell A6 instead of A1.

Thanks

 

[Robandemmy]

The names of the other sheets being:

269_NK Extrusion(Profiled) Extrusion(Profiled)CPX Calendaring (Flat) Tringles Cutter Complexing Finishing Confection Curing_OGen

<colgroup><col width="61"></colgroup><tbody>
</tbody>

 

[Fluff]

Does the code I supplied do what you want for the 1st sheet?

 

[Robandemmy]

Yes, It works perfectly!!

 

[Robandemmy]

This may add a lot more work to the code, but row R is useless too me, is copying P:Q and S:T too cumbersome?

 

[Fluff]

Ok, try

Sub copyData()
   Dim Lr As Long
   Dim i As Long
   Dim Ary As Variant
   
   Ary = Array("OTV", "269_NK", "Extrusion (Profiled)", "Extrusion(Profiled)CPX", "Calendaring (Flat)", "Tringles", "Cutter", "Complexing", "Finishing", "Confection", "Curing_OGen")
   For i = 0 To UBound(Ary)
      With Sheets(Ary(i))
         Lr = .Range("P" & Rows.Count).End(xlUp).Row
         Sheets("Routings").Range("A" & Rows.Count).End(xlUp).Offset(1).Resize(Lr - 7, 5).Value = .Range("P8:T" & Lr).Value
      End With
   Next i
End Sub

This assumes that A5 on Routings has data in before you run the macro

 

[Robandemmy]

I can an error with I pasted the new code in

 

[Robandemmy]

Tried to paste a screen shot there...the error is 400