Macro to copy last row in range

[tonywatsonhelp]

Hi Everyone,

I add my sales into sheet "Quotes" Range is D27:O51

The data for each sale is two rows,
row1 the details,
row2 Notes, (Not all sales have notes)

so what I want is a macro that will copy the last "Row1" sales details and paste it below but it needs to place it 2 rows down to miss the notes row.

So if the last row was 29, it would copy and paste into row 31 etc.
now to complicate things more, I only want to copy columns D to K and O

I've attached a table to try show what my sheet looks like. Please help if you can thanks Tony

[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD][/TD]
[TD]C[/TD]
[TD]D[/TD]
[TD]E[/TD]
[TD]F[/TD]
[TD]G[/TD]
[TD]H[/TD]
[TD]I[/TD]
[TD]J[/TD]
[TD]K[/TD]
[TD]L[/TD]
[TD]M[/TD]
[TD]N[/TD]
[TD]O[/TD]
[TD]P[/TD]
[TD]Q[/TD]
[/TR]
[TR]
[TD]26[/TD]
[TD][/TD]
[TD]Part[/TD]
[TD]Type[/TD]
[TD]number[/TD]
[TD]Price[/TD]
[TD]Qant[/TD]
[TD]sold[/TD]
[TD]%[/TD]
[TD]date[/TD]
[TD]country[/TD]
[TD]area[/TD]
[TD]time[/TD]
[TD]Person[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]27[/TD]
[TD]Details:[/TD]
[TD]A1[/TD]
[TD]R[/TD]
[TD]12[/TD]
[TD]22[/TD]
[TD]1[/TD]
[TD]Y[/TD]
[TD]100[/TD]
[TD]2018[/TD]
[TD]UK[/TD]
[TD]NW[/TD]
[TD]12[/TD]
[TD]TW[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]28[/TD]
[TD]Notes:[/TD]
[TD]this row[/TD]
[TD]does not count as[/TD]
[TD]the last row and[/TD]
[TD]is[/TD]
[TD]not [/TD]
[TD]copied[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]29[/TD]
[TD]Details:[/TD]
[TD]S3[/TD]
[TD]E[/TD]
[TD]11[/TD]
[TD]22[/TD]
[TD]1[/TD]
[TD]Y[/TD]
[TD]100[/TD]
[TD]2017[/TD]
[TD]USA[/TD]
[TD]NNT[/TD]
[TD]11[/TD]
[TD]TW[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]30[/TD]
[TD]Notes:[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]31[/TD]
[TD]Details:[/TD]
[TD]D2[/TD]
[TD]Q[/TD]
[TD]111[/TD]
[TD]11[/TD]
[TD]2[/TD]
[TD]N[/TD]
[TD]100[/TD]
[TD]2016[/TD]
[TD]UK[/TD]
[TD]NW[/TD]
[TD]1[/TD]
[TD]TW[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]32[/TD]
[TD]Notes:[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]33[/TD]
[TD]Details:[/TD]
[TD]P9[/TD]
[TD]P[/TD]
[TD]9[/TD]
[TD]9[/TD]
[TD]99[/TD]
[TD]Y[/TD]
[TD]99[/TD]
[TD]2018[/TD]
[TD]UK[/TD]
[TD]N[/TD]
[TD]2[/TD]
[TD]TW[/TD]
[TD]OK, so this is the last detals row so this would be copied[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]34[/TD]
[TD]Notes:[/TD]
[TD]THERE MIGHT BE[/TD]
[TD]NOTES[/TD]
[TD]HERE [/TD]
[TD]IT MIGHT BE BLANK[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]35[/TD]
[TD]Details:[/TD]
[TD]copied[/TD]
[TD]row[/TD]
[TD]would[/TD]
[TD]go[/TD]
[TD]here[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]36[/TD]
[TD]Notes:[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]37[/TD]
[TD]Details:[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]38[/TD]
[TD]Notes:[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]39[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

 

[davesexcel]

Find the 1st empty row in column d and see what is beside it, if it is "Notes:" then use the row below.

Sub Button1_Click()
    Dim LstRw As Long, r As Range
    
    LstRw = Cells(Rows.Count, "D").End(xlUp).Row + 1
    
    If Range("C" & LstRw) = "Notes:" Then LstRw = LstRw + 1
    
    Set r = Range("D" & LstRw & ":K" & LstRw + 1, "O" & LstRw & ":O" & LstRw + 1)
    
    r = "=IF(R[-2]C="""","""",R[-2]C)"
    r.Value = r.Value

End Sub

 

[Fluff]

How about

Sub GetLastDetails()
   Dim Fnd As Range
   
   Set Fnd = Range("D" & Rows.Count).End(xlUp)
   If Fnd.Offset(, -1).Value <> "Details:" Then Set Fnd = Fnd.Offset(-1)
   Fnd.Resize(, 12).Copy Fnd.Offset(2)
   Fnd.Offset(2, 8).Resize(, 3).ClearContents
End Sub

 

[tonywatsonhelp]

Thanks to both of you,
both great ideas and worked first time, so i've gone from no solution to two
thank you very much
Tony

 

[Fluff]

Glad we could help & thanks for the feedback