Copy last row of data and paste into specified range

[dgray21781]

I am trying to copy the last row of entered data from the range B8:O34 and paste it into the range B7:O7. When I run this macro I would like it to overwrite the data in A7:O7 and not continue listing the data in consecutive rows.

Sub CopyLastRow()
Dim lRw As Long
With ActiveSheet
lRw = Cells.Find("*", .Range("A" & Rows.Count), , , xlByRows, xlPrevious).Row
.Rows(lRw).Copy
End With
With Sheets("Sheet1")
lRw = Cells.Find("*", .Range("A" & Rows.Count), , , xlByRows, xlPrevious).Row
.Rows(lRw + 1).PasteSpecial Paste:=xlPasteValues
End With
End Sub

I have tried the above code to see if I can get the last row copying and work from there. This copies the very last line which is effectively a footer for the sheet and places it in row 40 on the destination sheet. I need it in a specified row.

 

[dgray21781]

Got it

Sub Button1_Click()
Dim sh As Worksheet, ws As Worksheet
Dim Rws As Long, rng As Range, Prng As Range

Set sh = ActiveSheet
Set ws = Sheets("Sheet1")
Set Prng = ws.Range("B6")

With sh
Rws = .Range("B34").End(xlUp).Row
Set rng = .Range("B" & Rws & ":O" & Rws)
End With

rng.Copy Prng

End Sub