How to add a new "fixed" value row into a table

Question

I'm working on an excel spreadsheet for personal monthly finance. My question is can you create an ActiveX Button that will add a fixed row to the bottom of a table when pressed?

The table looks like this:

Date         Description    Amount     Account
10/2/2017    Rent           $440.00    Checking(NF) 
10/3/2017    Groceries                 Checking(TD) 
10/3/2017    Transfer       $140.00    Checking(NF) 
10/3/2017    Transfer       ($140.00)  Credit Card(NF) 

I was looking to add a fixed row of: =TODAY() Rent $440.00 Checking(NF) to the bottom of the table above.

Thanks.

Answer 1

Use the following code. You could add a command button to the sheet with the pivot and associate the button with the following code:

Sub FindTableLastRow()
Dim wb As Workbook
Dim ws As Worksheet
Dim tbl As ListObject
Dim lRow As Long
Dim startCol As String * 1
Dim AddressArr() As String

Set wb = ThisWorkbook
Set ws = wb.Sheets(2) 'Adjust this to your sheet name with the table in.
Set tbl = ws.ListObjects("Table1") 'Adjust to your table name

AddressArr = Split(tbl.Range.Address, "$")   
startCol = AddressArr(1)
lRow = AddressArr(4)

ws.Cells(lRow + 1, startCol).FormulaR1C1 = Format(Date, "\ dd\/mm\/yyyy\")
ws.Cells(lRow + 1, startCol).Offset(0, 1).Value = "Rent"
ws.Cells(lRow + 1, startCol).Offset(0, 2).Value = "$440.00"
ws.Cells(lRow + 1, startCol).Offset(0, 3).Value = "Checking(NF)"

End Sub

It returns a string for the Date so it will appear last in the pivot as well when you sort on oldest to newest on Date column.