VBA help - identify last row and copy values, then id 2nd to last row and paste special

[dhaynes]

Hello all...coming to the experts for help again. I am trying to write a macro to
1.) identify the last row in a sheet
2.) copy the data in that row
3.) Paste that date in the last blank row
4.) Copy the data that is now in the 2nd to last row and paste special values

The code i have now (below) isn't working but it is not properly identifying the last blank row or the second to last row with data. Any and all suggestions are appreciated:

Sheets("Historical Pricing Source Data").Select
Range("A2787").Select
Selection.End(xlDown).Select
Rows("2797:2797").Select
Selection.Copy
Rows("2798:2798").Select
ActiveSheet.Paste
Rows("2797:2797").Select
Application.CutCopyMode = False
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False

 

[JLGWhiz]

Sub t()
Cells(Rows.Count, 1).End(xlUp).EntireRow.Copy Cells(Rows.Count, 1).End(xlUp)(2)
With Cells(Rows.Count, 1).End(xlUp).Offset(-1).EntireRow
    .Value = .Value
End With
End Sub

The statement below is misleading. The last blank row could be the bottom of the sheet.

Paste that date in the last blank row

 

[dhaynes]

Sub t()
Cells(Rows.Count, 1).End(xlUp).EntireRow.Copy Cells(Rows.Count, 1).End(xlUp)(2)
With Cells(Rows.Count, 1).End(xlUp).Offset(-1).EntireRow
    .Value = .Value
End With
End Sub

The statement below is misleading. The last blank row could be the bottom of the sheet.

Very helpful and you are absolutely correct. Thanks for pointing my misleading statement out.

 

[JLGWhiz]

We are al guilty of getting so wrapped up in what we are trying to do, that we don't really read what we write as if we were on the other end. I'll admit that I have to read my stuff back a couple of time to make sure I said what I meant to say. When I don't, it is subject to error, especially with failing eyesight and mind.
Regards, JLG