Macro to put data in last row in columns

[Cantrecallmyusername]

Hi there,

My spreadsheet has a table where I want the current date to inputted into starting at cell D18 and copy the cell value in F10 and paste this value into E18 when I run a macro. The next time I run the macro I want the data to move one row down and do the same thing each time the macro is ran.

So in E18 the date would be updated and the value would be copied from F10 once again and pasted into E19.
I would like this to be able to handle multiple runs of the macro.

I have used this -
last_cell = Cells.Find("*", SearchDirection:=xlPrevious) .Row
though cant seem to figure out how to include this in the above scenarios.

Thanks in advance

 

[igold]

You really did not give a lot of information about your table. This assumes that your table name is Table1 and that the first cell of the table is Cell D18. If that is not the case you will have make adjustments to the lines where indicated. The code is really intended as a jumping off point for you to get what you need.

The following is a good site for understanding tables, you may want to look at it...

The VBA Guide To ListObject Excel Tables

For a data analyst, Excel Tables are a necessity! They are the most efficient way to organize your raw data and refer to data that contracts or expands on a regular basis. Likewise, Excel tables can be extremely useful in combination with VBA.

www.thespreadsheetguru.com

Sub FillTable()

    Dim tbl As ListObject
    
    Set tbl = ActiveSheet.ListObjects("Table1")
    If Range("D18") = "" Then
        Range("D18") = Date
        Range("E18") = Range("F10")
        Exit Sub
    End If
    tbl.ListRows.Add Alwaysinsert:=True
    tbl.DataBodyRange(tbl.ListRows.Count, 1) = Date             'Adjust this line
    tbl.DataBodyRange(tbl.ListRows.Count, 2) = Range("F10")      'Adjust this line
  
End Sub