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]

Did you change the sheet names in the code on post#27?
If so did you make the same changes to the code in post#29?

 

[Robandemmy]

My bad, some tab names were edited. Now I get:

OTV 143
269_NK 104
Extrusion(Profiled) 101
Extrusion(Profiled) CPX 102
Calendering (Flat) 100
Tringles 56
Cutter 50
Complexing 29
Finishing 50
Confection 50
Curing_OGen 101

 

[Fluff]

With the corrected sheet names does this now work

Sub copyData()
   Dim Lr As Long
   Dim i As Long
   Dim ary As Variant
   
   ary = Array("OTV", "269_NK", "Extrusion(Profiled)", "Extrusion(Profiled) CPX", "Calendering (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

 

[Robandemmy]

No errors, but nothing gets copied

 

[Fluff]

If you run this what does the message box say

Sub Chk()
MsgBox Sheets("Routings").Range("A" & Rows.Count).End(xlUp).Offset(1).Row
End Sub

 

[Robandemmy]

7283

 

[Fluff]

That is the last row of data on the Routings sheet.
Does that make sense?

 

[Robandemmy]

It works!!

 

[Fluff]

Ok, in that case as you don't want col R add the line in blue

Sub copyData()
   Dim Lr As Long
   Dim i As Long
   Dim ary As Variant
   
   ary = Array("OTV", "269_NK", "Extrusion(Profiled)", "Extrusion(Profiled) CPX", "Calendering (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
[COLOR=#0000ff]   Sheets("Routings").Columns(3).Delete[/COLOR]
End Sub

 

[Robandemmy]

That works great! I can always remove them after the fact but is there something that I could add to only have cells with values be copied? Most cells are formula driven and are blank unless the Q column is poopulated