Selected Answer
Hello Abdo,
When I came back to post my suggested solution I noticed John had already offered one. I took a very similar approach to what John did but with a small twist.
After inserting the new row, I copied the formatting, and then copied any formulas that there might be. And at the end I updated the formulas in the "TOTAL" row. Here is the code I came up with:
Private Sub CommandButton1_Click()
Dim LR As Long ' last row (Total row) of table
Dim LRcopy As Long ' the row above the total row
Dim ws As Worksheet
Set ws = Sheets("GOODS")
With ws
LR = .Range("B" & Rows.Count).End(xlUp).Row
LRcopy = LR - 1
Rows(LR).Select
Selection.Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
' copy formats to new row
Rows(LRcopy).Select
Selection.Copy
Rows(LR).Select
Selection.PasteSpecial Paste:=xlPasteFormats, Operation:=xlNone, _
SkipBlanks:=False, Transpose:=False
Application.CutCopyMode = False
' copy formulas to new row
Rows(LRcopy).Select
Selection.Copy
Rows(LR).Select
Selection.PasteSpecial Paste:=xlPasteFormulas, Operation:=xlNone, _
SkipBlanks:=False, Transpose:=False
Application.CutCopyMode = False
' copy UserForm data to worksheet
.Range("B" & LR) = Date
.Range("C" & LR) = TextBox1.Text
.Range("D" & LR) = TextBox2.Text
.Range("E" & LR) = TextBox3.Text
.Range("F" & LR) = TextBox4.Text
End With
' reset textboxes to nothing (blank)
TextBox1.Value = ""
TextBox2.Value = ""
TextBox3.Value = ""
TextBox4.Value = ""
' update SUM formulas in TOTAL row
Range("B" & LR + 1).Select
ActiveCell.Offset(, 2).FormulaR1C1 = "=SUM(R1C:R[-1]C)" ' column D
ActiveCell.Offset(, 3).FormulaR1C1 = "=SUM(R1C:R[-1]C)" ' column E
ActiveCell.Offset(, 4).FormulaR1C1 = "=SUM(R1C:R[-1]C)" ' column F
End Sub
Cheers :-)