Excel VBA : add calculation to every tables in sam

问题:

this is a update version of here.

Desired output

Table

Thanks @sktneer who helped me out solving the first part of problem, now I want to

1. adjust on the code as I've added a few lines(rows) between table name and table, with added columns too
2. add the formula to the right side of the table with table name

I have included some of my assumptions on the code (as comments) from previous answer in "failed attempt" image.
Would you guys please let me know if it's correct?
Because I don't fully understand how did the code works even though I did googled on the statements.

回答1:

Try the below code. The code below will not insert the name of the class while making the Total summary. But the code will create a summary right to each table as you showed in your desired output, provided there is enough space for summary to the right of each table on the sheet otherwise the code will overwrite any data if found there.

Sub InsertSummaryForEachTable()
Dim ws As Worksheet
Dim rng As Range
Dim i As Integer, c As Long

Application.ScreenUpdating = False

Set ws = ActiveSheet

For Each rng In ws.UsedRange.SpecialCells(xlCellTypeConstants, 3).Areas
    If rng.Rows.Count > 1 And rng.Columns.Count = 4 Then
        c = rng.Cells(1, rng.Columns.Count).Column + 2
        Cells(rng.Rows(1).Row, c).Value = "Total"
        For i = 2 To rng.Rows.Count
            rng.Rows(i).Cells(1).Select
            Cells(rng.Rows(i).Row, c) = rng.Rows(i).Cells(1)
            Cells(rng.Rows(i).Row, c + 1) = "=SUM(" & rng.Rows(i).Address & ")"
        Next i
    End If
Next rng
Application.ScreenUpdating = True
End Sub