This code is just to look for the last cell then to copy its value in order to paste it into another sheet. I will show you 2 ways, looking from top to down and down to top, one works better than the other depending on what you want to do.

When I use the macro ?

To search for the last cell then to copy it.

How to create the macro ?

Read How to create, edit, hide and select a macro in an excel report

How to create the button to associate it with the macro ?

Read How to create a button and associated it to a macro in an excel report

How is the macro ?

Copy the code below and paste it into your macro. You will see my comments in green if exist so follow the help to adapt to your need.

To find the last cell before a blank cell (looking from top to down) then paste:

Sub test()
' change Sheet1 by your sheet name where to copy
' change Sheet2 by your sheet name where to paste and A2 by the cell to paste
Worksheets("Sheet1").Range("A:A").End(xlDown).Copy Sheets("Sheet2").Range("A2")
End Sub

To find the last cell after a blank cell (looking from down to top) then paste it:

Sub test()
' change Sheet1 by your sheet name where to copy
' change Sheet2 by your sheet name where to paste and A2 by the cell to paste
Worksheets("Sheet1").Range("A65536").End(xlUp).Copy Sheets("Sheet2").Range("A2")
End Sub

Just a quick note, to find the empty cell after the data cell:

Sub test()
' use 1 of those 2 options, it will find the empty cell of the column A
Range("A1").End(xlDown).Offset(1, 0).Select
Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).Select
' use 1 of those 2 options, it will find the empty cell of the row 1
Range("A1").End(xlToRight).Offset(0, 1).Select
Cells(1, Columns.Count).End(xlToLeft).Offset(0, 1).Select
End Sub