Almost every macro on this site finds its data by position — last used row, column C. That's fragile: insert a column and "Amount" is no longer column C. An Excel Table (a ListObject in VBA) gives the data a name and gives every column a name, so the macro can ask for `tbl.ListColumns("Amount")` and get the right column wherever it has moved to. Tables also grow automatically when rows are added, so there's no last-row arithmetic at all. It's the single change that makes a macro robust to the way people actually edit spreadsheets.

Sub TotalByHeaderName()
    Dim ws As Worksheet, tbl As ListObject
    Dim amountCol As ListColumn, cell As Range, total As Double

    Set ws = ThisWorkbook.Worksheets("Data")

    ' Refer to the table by NAME (Table Design → Table Name), not by position.
    On Error Resume Next
    Set tbl = ws.ListObjects("tblInvoices")
    On Error GoTo 0
    If tbl Is Nothing Then
        MsgBox "No table called tblInvoices on the Data sheet.", vbExclamation
        Exit Sub
    End If

    ' The column is found by its HEADER, so inserting columns can't break it.
    Set amountCol = tbl.ListColumns("Amount")

    ' DataBodyRange excludes the header and any totals row. It's Nothing
    ' when the table has no rows yet, so guard it.
    If Not amountCol.DataBodyRange Is Nothing Then
        For Each cell In amountCol.DataBodyRange
            If IsNumeric(cell.Value) Then total = total + cell.Value
        Next cell
    End If

    ' Add a row: the table grows, and formulas in other columns fill down.
    With tbl.ListRows.Add
        .Range(1, tbl.ListColumns("Customer").Index).Value = "Total"
        .Range(1, amountCol.Index).Value = total
    End With
End Sub

Paste into the Visual Basic Editor (Alt + F11 → Insert → Module), then run and save as macro-enabled (.xlsm).

• Read a column by name so the macro survives a reordered export.
• Append a row without working out where the data ends.
• Loop only the data rows, excluding the header and totals row automatically.

A simulation — press Run to perform what the macro does to a sample workbook.

• Find the column by header with Range.Find — Works on a plain range when converting to a table isn't possible. See the find-value snippet.
• Structured references in formulas — =SUM(tblInvoices[Amount]) gets the same robustness with no macro at all.