Copy data from numerious tabs and Paste into one tab

[gheyman]

I need to grab data from numerous tabs. I have some code that does that but I need to slightly modify it.

Sub Consolidate_BOEs()

'
    Dim ws As Worksheet
'
    For Each ws In Sheets
'
        If ws.Range("U1") = "54-TPL" Then
            ws.Range("B11:U1000").Copy
            Sheets("BOE Spreads").Cells(Rows.Count, "B").End(xlUp).Offset(1, 0).PasteSpecial xlPasteValues
        End If
'
     Next ws
'
End Sub

It would be better if it only copied down to the last row (column C) instead of all the way to row 1000. Is this possible?

 

[Fluff]

How about

Sub Consolidate_BOEs()
    Dim ws As Worksheet
    Dim Lr As Long
    
   For Each ws In Worksheets
      If ws.Range("U1") = "54-TPL" Then
         Lr = ws.Range("C" & Rows.Count).End(xlUp).Row
         ws.Range("B11:U" & Lr).Copy
         Sheets("BOE Spreads").Cells(Rows.Count, "B").End(xlUp).Offset(1, 0).PasteSpecial xlPasteValues
      End If
   Next ws
End Sub

 

[gheyman]

Thank you , once again

 

[Fluff]

You're welcome