Populate data in a different sheet using VBA

[ravikumarg]

Hello to the Awesome Ninjas!

I am trying to develop a simple tracker for my parents to manage their retirement expenses based on the fixed income cash-flows.

BACKGROUND
I am using MS Office 2013.

The sample tracker (attached) has four sheets. The first sheet gives an overview of expected credit(s) into their bank account that are not yet (manually) verified.
The second sheet allows them to easily record new bank deposits.

PROBLEM_STATEMENT
My problem is with the third and fourth sheets. I need to populate these sheets with the additional data needed for the tracker to work efficiently. I am looking for help to copy the data from the "Input" worksheet into the next empty row of the "FD_Master" sheet. Also need to auto-increment the FD_ID field.

I am confident that I can extend this logic to populate the "Payment_Master" worksheet, if I get any helpful pointers. I have pre-populated these sheets with some sample data for a better understanding of my problem/challenge.

Finally, I am trying to parse the records in the "Payment_Master" every time the workbook is opened to display the expected payouts from the fixed deposits that can then be verified against the various bank statements.

Appreciate any assistance or pointers regarding the VBA code to populate the "FD_Master" and/or the "Payment_Master"

Thanks in advance,
Ravi

 

[ravikumarg]

UPDATE: After mucking around these forum posts, I managed to populate the desired records using the following macro code. I would love to receive feedback on making this code more efficient/elegant or maintenance friendly.

Sub recordFD()
    Dim fdRef As String
    Dim fdBank As String
    Dim fdDate As Date
    Dim fdPayout As Long
    Dim fdPeriod As Long
    Dim fdAmount As Long
    Dim fdRate As Double
    Dim fdID As Long
  
    'Move the range values to local variables
    With Worksheets("Input")
        fdRef = Range("FD_REF").Value
        fdBank = Range("FD_BANK").Value
        fdDate = Range("FD_START").Value
        fdPayout = Range("FD_FREQ").Value
        fdPeriod = Range("FD_PERIOD").Value
        fdAmount = Range("FD_AMOUNT").Value
        fdRate = Range("FD_RATE").Value
    End With
  
    'Clear the input range as a feedback to the user.
    Range("C3:C9").ClearContents
  
    Dim lastRow As Long
    Dim eRow As Long
    'Select the FD_Master worksheet and find the last populated row number
    Worksheets("FD_Master").Select
    With Worksheets("FD_Master")
        lastRow = .Cells(.Rows.Count, "A").End(xlUp).Row
        'Calculate the row number for the entry of records
        eRow = lastRow + 1
        fdID = lastRow
        Range("A" & eRow).Value = lastRow
        Range("B" & eRow).Value = fdBank
        Range("C" & eRow).Value = fdDate
        Range("D" & eRow).Value = fdPeriod
        Range("E" & eRow).Value = fdPayout
        Range("F" & eRow).Value = fdAmount
        Range("G" & eRow).Value = fdRate
        Range("H" & eRow).Value = fdRef
    End With
  
    Dim i As Long
    Dim nextPaymentOn As Date
    Dim lastPayDate As Date
    Dim tPayouts As Long
    lastRow = 0
    eRow = 0
    'Select the Payment_Master worksheet and find the last populated row number
    Worksheets("Payment_Master").Select
    With Worksheets("Payment_Master")
        lastRow = .Cells(.Rows.Count, "A").End(xlUp).Row
        tPayouts = fdPeriod / fdPayout
        lastPayDate = fdDate
        For i = 1 To tPayouts
            'Calculate the row number for the entry of records
            eRow = lastRow + 1
          
            Range("A" & eRow).Value = lastRow
            Range("B" & eRow).Value = fdID
            nextPaymentOn = nextPayDate(lastPayDate, fdPayout)
            Range("C" & eRow).Value = nextPaymentOn
            lastPayDate = nextPaymentOn
            Range("D" & eRow).Value = fdAmount * fdRate / tPayouts
            Range("E" & eRow).Value = "No"
          
            'Increment the lastRow counter
            lastRow = lastRow + 1
        Next i
      
   End With
  
End Sub

Public Function nextPayDate(fdLastPayout As Date, fdPeriod As Long) As Date
    nextPayDate = DateAdd("m", fdPeriod, fdLastPayout)
End Function