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]

Try this mod

Lr = .Range("[COLOR=#ff0000]Q[/COLOR]" & Rows.Count).End(xlUp).Row

 

[Robandemmy]

That works great! Everything I need from columns P:T is being copied to where I need it, thank you! Would it be much to add columns J:N of worksheet "269_NK" to the top of the list and then all the P:T columns copy and paste after that?

 

[Fluff]

The problem with that, is that you cannot then delete the "R" column from Routings.

 

[Robandemmy]

That's ok, I can manually copy and paste those values. I thought it might be difficult, especially considering that the middle row (L) is useless, much like the R column.

Thanks for the help, I appreciate it!

 

[Fluff]

If you don't want col L then you can use

Sub copyData()
   Dim Lr As Long
   Dim i As Long
   Dim ary As Variant
   
   ary = Array("269_NK", "OTV", "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("Q" & Rows.Count).End(xlUp).Row
         If i = 0 Then
            Sheets("Routings").Range("A" & Rows.Count).End(xlUp).Offset(1).Resize(Lr - 7, 5).Value = .Range("J8:N" & Lr).Value
         End If
         Sheets("Routings").Range("A" & Rows.Count).End(xlUp).Offset(1).Resize(Lr - 7, 5).Value = .Range("P8:T" & Lr).SpecialCells(xlVisible).Value
      End With
   Next i
   Sheets("Routings").Columns(3).Delete
End Sub

 

[Robandemmy]

That edit works! What is the copy criteria? Certain tabs copy blank data...some ignore the blank cells while others do not. All the formulas used are =if(j8<>"",do whatever,"") so they all appear blank.

 

[Fluff]

It copies based on the last used row in col Q

 

[Robandemmy]

So the issue is only with the 269_NK tab. J:N and P:T each have formulas in rows 8-32. All 25 rows for J:N are copied while 7 rows are copied for P:T. All cells in each range are if statement formula based with the false value being "". They are also based on the input of another cell not in the range (F8:32). All other tabs copy only the cells with values.

 

[Fluff]

It is copying the same number of rows for both ranges as the value of Lr doesn't change for that sheet.
However when it copies the next sheet, it looks for the first blank cell in col A of "Routings".
If that is overwriting data, then what col can be used to find the last row?