Create a button when pressed to copy specific cells to a new sheet

Question

I am looking for a way to create a button that when pressed will only copy specific cells from the last row entered and then copy it into the first empty row on another sheet. This way if someone was to only hit update after 5 rows have been entered it will only copy the last row. 

An example would be I have entered data all the way up to the 5th row on Sheet 1 and decide I want to press the button to transfer cells A5, D5 and F5 to row 1 on Sheet2 into cells A1, B1 and C1.

I have some basic VB knowledge so I am looking for the syntax and I will be able to fill in the rest.

Answer 1

Like this:

Sub MyButton_Click()

    Dim r As Long

    r = Workheets("Sheet 1").Cells.Find(What:="*", _

        SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row

    Worksheets("Sheet 1").Range("A" & r).Copy Destination:=Worksheets("Sheet2").Range("A1")

    Worksheets("Sheet 1").Range("D" & r).Copy Destination:=Worksheets("Sheet2").Range("B1")

    Worksheets("Sheet 1").Range("F" & r).Copy Destination:=Worksheets("Sheet2").Range("C1")

    Application.CutCopyMode = False

End Sub