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!

 

[Robandemmy]

Ok, so I found where the error was coming from. The data from "confection" was in the correct rows. That has now been rectified and no errors exist when I run the code. Unfortunately, nothing happens now

 

[Fluff]

If it was copying data across before, but not now. Then it sounds like something has changed in your workbook.

 

[Fluff]

Cross posted https://www.excelforum.com/excel-pr...rom-multiple-tabs-to-one-tab.html#post4938790

While we do not prohibit Cross-Posting on this site, we do ask that you please mention you are doing so and provide links in each of the threads pointing to the other thread (see rule 13 here along with the explanation: Forum Rules).
This way, other members can see what has already been done in regards to a question, and do not waste time working on a question that may already be answered.

 

[Robandemmy]

Sorry about that! I was just looking to expand the reach of my question and didn't really consider the consequences, my apologies! Some frustration also crept in when I went back and had this code working:

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

End Sub

But this one would not

Sub copyData() Dim Lr As Long Dim i As Long Dim Ary As Variant Ary = Array("269_NK", "Extrusion(Profiled)", "Extrusion(Profiled) CPX", "Calendering (Flat)", "Tringles", "Cutter", "Complexing", "Confection", "Finishing", "Curing_OGen", "OTV") For i = 0 To UBound(Ary) With Sheets(Ary(i)) Lr = .Range("P" & Rows.Count).End(xlUp).Row Sheets("Capture 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 Once again, sorry for the infraction. I truly appreciate the assistance and will therefore respect that going forward.

<tbody>
</tbody>

 

[Fluff]

Can you share your workbook?
If so you will need to upload to a share site such as OneDrive, Dropbox. Mark for sharing & post the link to the thread.

 

[Robandemmy]

Unfortunately, due to the nature of the data in the worksheet, I am unable to upload it.

 

[Fluff]

OK, fair enough.
1st make sure that the code is in a regular module, not a sheet module, or the Thisworkbook module.
2nd in the VB Editor below the main code page should be another window called Immediate. If this is not visible Ctrl G should bring it up.
Then run this

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)
      Debug.Print ary(i), Evaluate("isref('" & ary(i) & "'!A1)")
   Next i
End Sub

& then copy paste the result from the immediate window to the thread

 

[Robandemmy]

OTV True
269_NK True
Extrusion(Profiled) True
Extrusion(Profiled) CPX True
Calendering (Flat) True
Tringles True
Cutter True
Complexing True
Finishing True
Confection True
Curing_OGen True

 

[Fluff]

Ok, that's fine. Now

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
         Debug.Print ary(i), Lr
      End With
   Next i
End Sub

 

[Robandemmy]

OTV 143
269_NK 104

And then Run-time error '9'
Subscript out of range