DynamicLastRow

[soidog]

Hello,
I've been searching a lot but can't find the answer.
There are a number of cells with text string "DATE" in column "A".
Under every "DATE" there are 0-5 rows with dates, followed by an empty row.
I want to copy the "DATE"-row and the following rows with dates (0-5), offset 11 columns, to column "T" (pls. see code).
Any help is much appreciated.
This is what I have so far:

Sub LastRow()


Dim c       As range
Dim FA      As String
Dim ws      As Worksheet
Dim LR      As Long


Application.ScreenUpdating = False
Set ws = ActiveSheet


With ws.Columns(1)
    Set c = Cells.Find(What:="DATE", LookAt:=xlWhole)
    FA = c.Address
    LR = range("A:A").CurrentRegion.Rows.Count  'Wrong?
    
    Do
        'range(c, c.Offset(5, 11)).Copy     'This works (static 5 rows)
        range(c, c.Offset(LR, 11)).Copy     'This works not. Rows can be 0-5, not only 5, until first empty row.


        range("T" & Rows.Count).End(xlUp).Offset(2).PasteSpecial


        Set c = .FindNext(c)
    Loop Until c Is Nothing Or c.Address = FA
End With
Application.ScreenUpdating = True
End Sub

/Tom

 

[soidog]

Thank you Scott T, but now the code stops after first paste in column "T".

 

[Scott T]

The code runs fine for me both Excel 2010 and 2016 (office 365).

Not sure why it would not work for you.