VBA for New Table Data Calculations

[beginvbaanalyst]

Good Afternoon Everyone,

I'm starting to dial back in on creating macros and I'm wondering if there's a way to either capture all of my left column formulas and drag them over to the right while also capturing the changes between each formula.

For example (left):
=SUM(SUMIFS(_S22[Balance],_S22[Type],{"100 011","100 012","100 013","500 011","500 012","800 001","800 002","800 004","800 009","800 010","900 011","900 012","98","99"}))
Right:
=SUM(SUMIFS(_O22[Balance],_O22[Type],{"100 011","100 012","100 013","500 011","500 012","800 001","800 002","800 004","800 009","800 010","900 011","900 012","98","99"}))

The table changes but the type of data stays the same. Is there a way to capture this for data tables moving forward with vba or am I overthinking and there are formulas that when I drag adjacent from the column it automatically chooses the next table (for instance O22).

 Range("R2").Select
    Selection.Copy
    Range("S2").Select
    ActiveSheet.Paste
    Application.CutCopyMode = False
    ActiveCell.FormulaR1C1 = _
        "=SUM(SUMIFS(_SS22[Balance],_SS22[Type],{""100 011"",""100 012"",""100 013"",""500 011"",""500 012"",""800 001"",""800 002"",""800 004"",""800 009"",""800 010"",""900 011"",""900 012"",""98"",""99"",""800 013""}))"
    Range("R3").Select
    Selection.Copy
    Range("S3").Select
    ActiveSheet.Paste
    Application.CutCopyMode = False
    ActiveCell.FormulaR1C1 = _
        "=SUM(SUMIFS(_O22[Balance],_O2223[Type],{""100 011"",""100 012"",""100 013"",""500 011"",""500 012"",""800 001"",""800 002"",""800 004"",""800 009"",""800 010"",""900 011"",""900 012"",""98"",""99"",""800 013""}))"
    Range("S4").Select
End Sub

My current VBA is adjusting for the next set of data (O22) but the VBA won't know what the table will be called so can it identify as the next table created in the workbook?