Adding Next Month Programmatically (VBA) to a Dynamic Range Table in Excel

Question

I have a dynamic ranged table [Defined Table Name = MainTable]. I also have code which allows me to find the last cell in DateRange (Defined Name Range for Column A).

Sub Last_Row_Range()
Dim nextMonth As Range
Set nextMonth = Sheets("MainTable").Range("A" & Rows.Count).End(xlUp).Offset(1, 0)
nextMonth.Select
End Sub

However, here is what I can't figure out. I want the user to be able to press a button (btnNextMonth), which automatically goes to the last row in DateRange, offsets by 1 AND Automatically adds the next month. Below is a picture of what I am trying to achieve. Thanks in advance guys.

score 1 · Accepted Answer · answered Feb 09 '17 at 22:25

Try using the table to your advantage instead of manually finding the last row and inserting. Here is an example:

Sub Last_Row_Range()
    'Get reference to table
    Dim tbl As ListObject
    Set tbl = Sheets("MainTable").Range("MainTable").ListObject

    'Insert new row in table
    Dim tblRow As ListRow
    Set tblRow = tbl.ListRows.Add(AlwaysInsert:=True)

    'Increment previous date by 1 month and place in new row
    tblRow.Range(1, 1) = DateAdd("m", 1, tblRow.Range(0, 1).Value)
End Sub

Tested

Thanks @Portland Runner. Works perfectly! – Jeremy Scott Feb 10 '17 at 16:36 — Jeremy Scott, Feb 10 '17 at 16:36

Adding Next Month Programmatically (VBA) to a Dynamic Range Table in Excel

1 Answers1