Add Formula With VBA

[saschultz]

I have created a proposal process tracking sheet for my team and have included an "add new client" button. The "add new client" button copies information from a maintenance tab and pastes it at an offset from a cell named "OneAbove" below the last entry on the Proposal Schedule.

Sub InsertNewClientMacro()

On Error Resume Next

'Copy the NewClient range from the maintenance tab
    Range("NewClient").Copy

'Find cell OneAbove and Paste down the NewClient in OneAbove
    Range("OneAbove").Select
    Selection.Insert Shift:=xlDown
    
'Insert Data Validation in NewClient Assigned To Section
    Range("OneAbove").Resize(19, 1).Offset(-19, 2).Select
    With Selection.Validation
        .Delete
        .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _
        xlBetween, Formula1:="=Maintenance!$C$2:$C$15"
        .IgnoreBlank = True
        .InCellDropdown = True
        .InputTitle = ""
        .ErrorTitle = ""
        .InputMessage = ""
        .ErrorMessage = ""
        .ShowInput = True
        .ShowError = True
     End With

'Copy CalendarDrag from Maintenance and Paste to Proposal Schedule
    Range("CalendarDrag20").Copy Range("OneAbove").Offset(-20, 7)

Once the user has completed the relevant information, there is another button that calls a macro and adds 19 rows to a table on the Filtered Schedule tab called "ScheduleTable" along with copying and pasting the information from the newly added client on the Proposal Schedule tab.

'Copy info to ScheduleTable
    Dim LastRow As Range
    Dim ScheduleTable As ListObject
    Dim FilteredSchedule As Worksheet
    Dim ProposalSchedule As Worksheet
    
    'Add row to bottom of Schedule Table
    Worksheets("Filtered Schedule").ListObjects("ScheduleTable").ListRows.Add
    
    Set ScheduleTable = Worksheets("Filtered Schedule").ListObjects("ScheduleTable")
    Set LastRow = ScheduleTable.ListRows(ScheduleTable.ListRows.Count).Range
    Set ProposalSchedule = Worksheets("Proposal Schedule")
    
    
    With LastRow
    
    'add rows & formulas
        .Cells(1, 1) = Range("OneAbove").Offset(-19, 2)
        .Cells(1, 2) = Range("OneAbove").Offset(-19)
        .Cells(1, 3) = Range("OneAbove").Offset(-20)
        .Cells(1, 4) = Range("OneAbove").Offset(-19, 4)
        
        .Cells(2, 1) = Range("OneAbove").Offset(-18, 2)
        .Cells(2, 2) = Range("OneAbove").Offset(-18)
        .Cells(2, 3) = Range("OneAbove").Offset(-20)
        .Cells(2, 4) = Range("OneAbove").Offset(-18, 4)
        
        .Cells(3, 1) = Range("OneAbove").Offset(-17, 2)
        .Cells(3, 2) = Range("OneAbove").Offset(-17)
        .Cells(3, 3) = Range("OneAbove").Offset(-20)
        .Cells(3, 4) = Range("OneAbove").Offset(-17, 4)
              
        .Cells(4, 1) = Range("OneAbove").Offset(-16, 2)
        .Cells(4, 2) = Range("OneAbove").Offset(-16)
        .Cells(4, 3) = Range("OneAbove").Offset(-20)
        .Cells(4, 4) = Range("OneAbove").Offset(-16, 4)
        
        .Cells(5, 1) = Range("OneAbove").Offset(-15, 2)
        .Cells(5, 2) = Range("OneAbove").Offset(-15)
        .Cells(5, 3) = Range("OneAbove").Offset(-20)
        .Cells(5, 4) = Range("OneAbove").Offset(-15, 4)
        
        .Cells(6, 1) = Range("OneAbove").Offset(-14, 2)
        .Cells(6, 2) = Range("OneAbove").Offset(-14)
        .Cells(6, 3) = Range("OneAbove").Offset(-20)
        .Cells(6, 4) = Range("OneAbove").Offset(-14, 4)
        
        .Cells(7, 1) = Range("OneAbove").Offset(-13, 2)
        .Cells(7, 2) = Range("OneAbove").Offset(-13)
        .Cells(7, 3) = Range("OneAbove").Offset(-20)
        .Cells(7, 4) = Range("OneAbove").Offset(-13, 4)
        
        .Cells(8, 1) = Range("OneAbove").Offset(-12, 2)
        .Cells(8, 2) = Range("OneAbove").Offset(-12)
        .Cells(8, 3) = Range("OneAbove").Offset(-20)
        .Cells(8, 4) = Range("OneAbove").Offset(-12, 4)
        
        .Cells(9, 1) = Range("OneAbove").Offset(-11, 2)
        .Cells(9, 2) = Range("OneAbove").Offset(-11)
        .Cells(9, 3) = Range("OneAbove").Offset(-20)
        .Cells(9, 4) = Range("OneAbove").Offset(-11, 4)
              
        .Cells(10, 1) = Range("OneAbove").Offset(-10, 2)
        .Cells(10, 2) = Range("OneAbove").Offset(-10)
        .Cells(10, 3) = Range("OneAbove").Offset(-20)
        .Cells(10, 4) = Range("OneAbove").Offset(-10, 4)
        
        .Cells(11, 1) = Range("OneAbove").Offset(-9, 2)
        .Cells(11, 2) = Range("OneAbove").Offset(-9)
        .Cells(11, 3) = Range("OneAbove").Offset(-20)
        .Cells(11, 4) = Range("OneAbove").Offset(-9, 4)
        
        .Cells(12, 1) = Range("OneAbove").Offset(-8, 2)
        .Cells(12, 2) = Range("OneAbove").Offset(-8)
        .Cells(12, 3) = Range("OneAbove").Offset(-20)
        .Cells(12, 4) = Range("OneAbove").Offset(-8, 4)
        
        .Cells(13, 1) = Range("OneAbove").Offset(-7, 2)
        .Cells(13, 2) = Range("OneAbove").Offset(-7)
        .Cells(13, 3) = Range("OneAbove").Offset(-20)
        .Cells(13, 4) = Range("OneAbove").Offset(-7, 4)
        
        .Cells(14, 1) = Range("OneAbove").Offset(-6, 2)
        .Cells(14, 2) = Range("OneAbove").Offset(-6)
        .Cells(14, 3) = Range("OneAbove").Offset(-20)
        .Cells(14, 4) = Range("OneAbove").Offset(-6, 4)
        
        .Cells(15, 1) = Range("OneAbove").Offset(-5, 2)
        .Cells(15, 2) = Range("OneAbove").Offset(-5)
        .Cells(15, 3) = Range("OneAbove").Offset(-20)
        .Cells(15, 4) = Range("OneAbove").Offset(-5, 4)
           
        .Cells(16, 1) = Range("OneAbove").Offset(-4, 2)
        .Cells(16, 2) = Range("OneAbove").Offset(-4)
        .Cells(16, 3) = Range("OneAbove").Offset(-20)
        .Cells(16, 4) = Range("OneAbove").Offset(-4, 4)
        
        .Cells(17, 1) = Range("OneAbove").Offset(-3, 2)
        .Cells(17, 2) = Range("OneAbove").Offset(-3)
        .Cells(17, 3) = Range("OneAbove").Offset(-20)
        .Cells(17, 4) = Range("OneAbove").Offset(-3, 4)
        
        .Cells(18, 1) = Range("OneAbove").Offset(-2, 2)
        .Cells(18, 2) = Range("OneAbove").Offset(-2)
        .Cells(18, 3) = Range("OneAbove").Offset(-20)
        .Cells(18, 4) = Range("OneAbove").Offset(-2, 4)
        
        .Cells(19, 1) = Range("OneAbove").Offset(-1, 2)
        .Cells(19, 2) = Range("OneAbove").Offset(-1)
        .Cells(19, 3) = Range("OneAbove").Offset(-20)
        .Cells(19, 4) = Range("OneAbove").Offset(-1, 4)

End With

'Add row to Client Drop Down options
  
'Add row to Client Drop Down options
    Dim LastRow2 As Range
    Dim Client As ListObject
    Dim Maintenance As Worksheet
    Dim ProposalSchedule As Worksheet
    
    'Add row to bottom of Client Table
    Worksheets("Maintenance").ListObjects("Client").ListRows.Add
    
    Set Client = Worksheets("Maintenance").ListObjects("Client")
    Set LastRow2 = Client.ListRows(Client.ListRows.Count).Range
    Set ProposalSchedule = Worksheets("Proposal Schedule")
    
    With LastRow2
        .Cells(1, 1) = Range("OneAbove").Offset(-20)
    End With

End Sub

Rather than having the user have to click the second button and manually update the table if changes are made, I would like to create a code that adds a formula to the new rows on the table so the new client information can be dynamic in the table.

Thank you!