Option Explicit

Dim shtCompanyA As Worksheet
Dim shtSupplier1 As Worksheet
Dim shtSupplier2 As Worksheet
Dim shtCurrentSupplier As Worksheet

Private Sub OKButton_Click()

UpdateSheets
Me.Hide

End Sub

Private Sub CancelButton_Click()
Me.Hide
End Sub


Private Sub UpdateAndNewButton_Click()
UpdateSheets

'reset the form for the next entry
SupplierComboBox.Text = ""
DateTextBox.Text = Format(Now(), "mm/dd")
End Sub

Private Sub UserForm_Activate()
DateTextBox.Text = Format(Now(), "mm/dd")
End Sub

Private Sub UserForm_Initialize()
SupplierComboBox.AddItem ("Supplier1")
SupplierComboBox.AddItem ("Supplier2")
Set shtCompanyA = Sheets("CompanyA")
Set shtSupplier1 = Sheets("Supplier1")
Set shtSupplier2 = Sheets("Supplier2")
End Sub

Sub UpdateSheets()
Dim lNextCompanyARow As Long
Dim lNextSupplierRow As Long

'Get the next available company row to enter data
lNextCompanyARow = WorksheetFunction.CountA(shtCompanyA.Range("A:A")) + 1

'set the supplier worksheet we'll be entering data onto
If SupplierComboBox.Text = "Supplier1" Then
    Set shtCurrentSupplier = shtSupplier1
ElseIf SupplierComboBox.Text = "Supplier2" Then
    Set shtCurrentSupplier = shtSupplier2
'-----------------------------------------------------------------
'For every available supplier, just add another "ElseIf" block
'-----------------------------------------------------------------
End If

'Get the next available supplier row to enter data
lNextSupplierRow = WorksheetFunction.CountA(shtCurrentSupplier.Range("A:A")) + 1

'enter the values in CompanyA
With shtCompanyA
    .Cells(lNextCompanyARow, 1) = DateTextBox.Text
    .Cells(lNextCompanyARow, 2) = SupplierComboBox.Text
    '----------------------------------------------------------------------------------
    'for each additional field, just add another ".Cells(lNextCompanyARow, 1) = " line
    '------------------------------------------------------------------------------------
End With

'update the current supplier sheet
With shtCurrentSupplier
    .Cells(lNextSupplierRow, 1) = DateTextBox.Text
    '----------------------------------------------------------------------------------
    'for each additional field, just add another ".Cells(lNextSupplierRow, 1) = " line
    '------------------------------------------------------------------------------------
End With

End Sub

Option Explicit

Private Sub UpdateSupplierButton_Click()
'------------------------------------------------------------------------------------------------------------
'This will take the last row of data on the CompanyA worksheet and enter it into whichever supplier is entered into the "B"
    'column on the CompanyA worksheet.
'Whatever value you enter into the supplier ("B") column, there must be a worksheet with that name in the workbook.
    'I would suggest setting up data validation for the supplier column so that you cannot make typos
'--------------------------------------------------------------------------------------------------------------
Dim shtCompanyA As Worksheet
Dim shtCurrentSupplier As Worksheet
Dim lLastCompanyARow As Long
Dim lNextSupplierRow As Long

'Date Company Payment Amount Invoice No. Comments field variables
Dim dtDate As Date
Dim stCompany As String
Dim dPayment As Double
Dim stInvoiceNum As String
Dim stComments As String

'If you want to rename the "CompanyA" worksheet, rename it in the following line as well
Set shtCompanyA = Sheets("CompanyA")

'Get the last company row data entered
lLastCompanyARow = WorksheetFunction.CountA(shtCompanyA.Range("A:A"))

'set the supplier worksheet we'll be entering data onto
With shtCompanyA
    '------------------------------------------------------------------------------------------------------------------------
    'if you want to change the column that recieves the supplier name, in the following line, ".Cells(lLastCompanyARow, 2)",
        'the 2 represents the "B" column, 3 would represent the "C" column, and so on.
    'Also, if you want to rename the "Supplier1" etc. sheets, here is where you'd rename them in the code so that the code
        'still works
    '------------------------------------------------------------------------------------------------------------------------
    If .Cells(lLastCompanyARow, 2) = "Supplier1" Then
        Set shtCurrentSupplier = Sheets("Supplier1")
    '-----------------------------------------------------------------
    'For every available supplier, just add another "ElseIf" block like the one below
    '-----------------------------------------------------------------
    ElseIf .Cells(lLastCompanyARow, 2) = "Supplier2" Then
        Set shtCurrentSupplier = Sheets("Supplier2")
    Else
        MsgBox "No worksheet exists with that supplier name.", vbCritical
        End
    End If
    
    'Get the values from the last row of CompanyA
    '-----------------------------------------------------------------
    'This assumes that the fields are in the exact order as your original post and you want to put each field onto the supplier
        'worksheet. Edit as necessary.
    '-----------------------------------------------------------------
    dtDate = .Cells(lLastCompanyARow, 1)
    stCompany = .Cells(lLastCompanyARow, 2)
    dPayment = .Cells(lLastCompanyARow, 3)
    stInvoiceNum = .Cells(lLastCompanyARow, 4)
    stComments = .Cells(lLastCompanyARow, 5)
End With

'Get the next available supplier row to enter data
'-------------------------------------------------------------------------------------------------------
'This assumes you have no blank rows above your headers. If you do, the "+1" ending of the next line will need to be adjusted
'-------------------------------------------------------------------------------------------------------
lNextSupplierRow = WorksheetFunction.CountA(shtCurrentSupplier.Range("A:A")) + 1

'update the current supplier sheet
With shtCurrentSupplier
    .Cells(lNextSupplierRow, 1) = dtDate
    .Cells(lNextSupplierRow, 2) = stCompany
    .Cells(lNextSupplierRow, 3) = dPayment
    .Cells(lNextSupplierRow, 4) = stInvoiceNum
    .Cells(lNextSupplierRow, 5) = stComments
End With

'Release computer's memory
Set shtCompanyA = Nothing
Set shtCurrentSupplier = Nothing
End Sub