Hello again!
I have a userform that adds data to the first empty row within a certain range. What this does now, if there is a Total Row in my table, is adds the data under the total row. This means it is not being added to my table.
Is there a way to adjust my current code to add the data to the last row in my table, but above the total row?
I have a userform that adds data to the first empty row within a certain range. What this does now, if there is a Total Row in my table, is adds the data under the total row. This means it is not being added to my table.
Is there a way to adjust my current code to add the data to the last row in my table, but above the total row?
Code:
Private Sub cmdbtnSave_Click()
'Error if Date not formatted correctly
If Not IsDate(Me.txtDate.Text) Then
MsgBox "The Data entered is not a Date." & Chr(10) & "Please re-format it as appropriate."
Exit Sub
End If
Dim emptyRow As Long
'Make Sheet3 (Account Register) active
Sheet3.Activate
'Determine emptyRow
emptyRow = WorksheetFunction.CountA(Range("B:B")) + 1
'Transfer information
Cells(emptyRow, 2).Value = combAccount.Value
Cells(emptyRow, 3).Value = txtDate.Value
Cells(emptyRow, 4).Value = txtRef.Value
Cells(emptyRow, 5).Value = txtPayee.Value
If optbtnYes.Value = True Then
Cells(emptyRow, 6).Value = "Yes"
Else
Cells(emptyRow, 6).Value = "No"
End If
Cells(emptyRow, 7).Value = combCategory.Value
Cells(emptyRow, 8).Value = txtMemo.Value
If combStatus.Value = "Reconciled" Then
Cells(emptyRow, 9).Value = "R"
Else
If combStatus.Value = "Cleared" Then
Cells(emptyRow, 9).Value = "C"
Else
Cells(emptyRow, 9).Value = "V"
End If
End If
If btnIncome.Value = False Then
Cells(emptyRow, 10).Value = txtIncome
Else
Cells(emptyRow, 11).Value = txtIncome
End If
Cells(emptyRow, IIf(btnIncome.Value, 11, 10)).Value = CCur(Val(txtIncome.Value))
'Automatic sorting
Dim ws As Worksheet
Set ws = ActiveSheet
Dim target_table As ListObject
Set target_table = ws.ListObjects("AccountRegister")
Dim sort_column_index As Long
sort_column_index = target_table.ListColumns("Date").Index
Dim sort_column As Range
Set sort_column = target_table.ListColumns(sort_column_index).Range
'Apply the sorting to the table
With target_table.Sort
.SortFields.Clear
.SortFields.Add Key:=sort_column _
, SortOn:=xlSortOnValues, Order:=xlAscending _
, DataOption:=xlSortNormal
.Apply
End With
ActiveSheet.Protect "", True, True, AllowFiltering:=True, AllowSorting:=True
Unload Me
End Sub
Last edited: