EXCEL: adding records using macro /duplicate records added

Question

Excel 2019 running adding data records (I'm not a programmer, but this should be easy enough if the DATA ENTRY FORM function from older excel version was not taken out) I've created a data input sheet to update a running database (on another sheet) created a macro sub that add's the initial record When I need to add the next record, it replaces the previous record and adds duplicate record.

I'm able to create the 1st record with success. adding the next distinct record is where I am failing.

Code below is revised from research:VBA Entering userform data at next blank row correctly

My macro as follows:

Sub UpdateComplaintsTest()

' UpdateComplaintTest Macro

    Set ws = Sheets("ACH Complaints 2019")

    LastRow = ws.Range("A" & Rows.Count).End(xlUp).Row + 1 'Finds the last blank row

    ws.Range("A" & LastRow).Value = "=ACHComplaintsForm!B3" 'Inserts the Date Col A
    ws.Range("A" & LastRow).Offset(0, 1).Value = "=ACHComplaintsForm!B4" 'Inserts Time Col B
    ws.Range("B" & LastRow).Offset(0, 1).Value = "=ACHComplaintsForm!B5" 'Inserts Name of Complainant Col C
    ws.Range("C" & LastRow).Offset(0, 1).Value = "=ACHComplaintsForm!B6" 'Sender's Contact No Col D
    ws.Range("D" & LastRow).Offset(0, 1).Value = "=ACHComplaintsForm!B7" 'Sender's Email Col E
    ws.Range("E" & LastRow).Offset(0, 1).Value = "=ACHComplaintsForm!B8" 'Date of Transaction Col F
    ws.Range("F" & LastRow).Offset(0, 1).Value = "=ACHComplaintsForm!B9" 'Time of Transaction Col G
    ws.Range("G" & LastRow).Offset(0, 1).Value = "=ACHComplaintsForm!B10" 'Transaction Ref No Col H
    ws.Range("H" & LastRow).Offset(0, 1).Value = "=ACHComplaintsForm!B11" 'Mode of Tran / Online/Mobile Col I
    ws.Range("I" & LastRow).Offset(0, 1).Value = "=ACHComplaintsForm!B12" 'Name of Clearing House Col J
    ws.Range("J" & LastRow).Offset(0, 1).Value = "=ACHComplaintsForm!B13" 'Sending Bank Col K
    ws.Range("K" & LastRow).Offset(0, 1).Value = "=ACHComplaintsForm!B14" 'Receiving Bank Col L
    ws.Range("L" & LastRow).Offset(0, 1).Value = "=ACHComplaintsForm!B15" 'Amount Col M
    ws.Range("M" & LastRow).Offset(0, 1).Value = "=ACHComplaintsForm!B16" 'Receiver Name Col N
    ws.Range("N" & LastRow).Offset(0, 1).Value = "=ACHComplaintsForm!B17" ' Receiver Contact No Col O
    ws.Range("O" & LastRow).Offset(0, 1).Value = "=ACHComplaintsForm!B18" 'Receiver Email Col P
    ws.Range("P" & LastRow).Offset(0, 1).Value = "=ACHComplaintsForm!B19" 'Receiver AccountNo Col Q
    ws.Range("Q" & LastRow).Offset(0, 1).Value = "=ACHComplaintsForm!B20" 'History of Trans Col R
    ws.Range("R" & LastRow).Offset(0, 1).Value = "=ACHComplaintsForm!B21" 'Action 1 Col S
    ws.Range("S" & LastRow).Offset(0, 1).Value = "=ACHComplaintsForm!B22" 'Action 2 Col T
    ws.Range("T" & LastRow).Offset(0, 1).Value = "=ACHComplaintsForm!B23" 'Action 3 Col U

End Sub

Expected Result: additional entries from the data input sheet should CREATE a new record on the next row.

Answer 1

Possibly simple transposition

Assuming that you add new userform data into an extra form sheet's right most wandering column and you just want to write the collected data back horizontally to a target sheet, you could use the following approach via Application.Transpose in order to interchange rows and column(s) of an intermediate formdata array.

 Option Explicit                 ' declaration head of Code module

 Sub UpdateComplaintsTest()

    ' [1] assign vertical data column to 2-dimensioned 1-based array formdata
          Dim formdata()         As Variant
          formdata = getFormData("ACHComplaintsForm")    
    ' [2] write data horizontally (i.e. transpose data column from variant array formdata)                                            
          nextTargetRange("ACH Complaints 2019", UBound(formdata), "A").Value = Application.Transpose(formdata)

End Sub

Helper function getFormData() called by section [1]

It's possible to assign an entire range to a variant array by one code line, e.g. via formdata = Thisworkbook.Worksheets("XY").Range("B3:Z1000").Value. As the right assignment part in section [1] gets executed by the following function calculating the most right values in the form data sheet, you are coding formdata = getFormData("ACHComplaintsForm") instead.

Furthermore the function resizes the returned data range to 1 column, i.e. the most right column in source data ACHComplaintsForm (where the sheet name is passed as a string argument and the starting row defaulting to 3 can be indicated optionally).

Function getFormData(ByVal DataSheet As String, Optional ByVal StartRow As Long = 3) As Variant()
' Purpose: return 2-dim 1-based array containing latest data column (i.e. most right column)
' Note:    Function assumes data start at 3rd row
    With ThisWorkbook.Worksheets(DataSheet)
        '[a] define number of most right column
             Dim nextCol As Long
             nextCol = .Cells(StartRow, .Columns.Count).End(xlToLeft).Column
        '[b] define number of items in this data column
             Dim Itemscount  As Long
             Itemscount = .Cells(.Rows.Count, nextCol).End(xlUp).Row - StartRow + 1

        '[c] return column data as variant 2-dim 1-based array
             getFormData = .Cells(StartRow, nextCol).Resize(Itemscount, 1).Value
             'Debug.Print "Form Data Range " & .Cells(StartRow, nextCol).Resize(Itemscount, 1).Address
    End With

End Function

Helper function nextTargetRange() called by section [2]

This function simply resizes the target row range to the necessary size to receive the indicated number of source items.

Function nextTargetRange(ByVal TargetSheet As String, Itemscount As Long, Optional ByVal StartCol As String = "A") As Range
' Purpose: return next free row range to receive needed data starting at a given column
  With ThisWorkbook.Worksheets(TargetSheet)
    ' [a] define next free row
          Dim nextFreeRow As Long
          nextFreeRow = .Range(StartCol & Rows.Count).End(xlUp).Row + 1
    ' [b] return function result, i.e. the receiving target range
          Set nextTargetRange = .Range(StartCol & nextFreeRow).Resize(1, Itemscount)
          'Debug.Print "Target Range " & nextTarget.Address
  End With
End Function