This macro creates a table of sheet names with formulas linking the total cells.
Using your example this is what the output looks like for just K to M (the code does K to Q):
A B C D
1 Sheet name Totals 1 Totals 2 Totals 3
2 1101 $100 $200 $5,000
3 1102 $200 $500 $600
4 1103 $400 $900 $2,500
Cell B2 contains ='1101'!K750
Cell D4 contains ='1103'!M750
You can change the code to link to more cells (columns) - its a simple for next loop, currently n = 1 to 7
As this macro only needs to be run when you add new worksheets, it doesn't need to be attached to a command button. Just put it in a standard module (not a class module), attached to the workbook. Then place the cursor anywhere inside the code, after the opening sub, and click f5, or use f8 to single step through it to see what is happeneing.
Private Sub TotalsFormulas()
Dim wsEach As Worksheet
Dim intRowCount As Integer
Dim n As Integer
'set row offset
intRowCount = 0
'loop through all worksheets
For Each wsEach In .Worksheets()
'only get data if the worksheet is not the Master
If wsEach.Name <> "Master" Then
'copy worksheet name to next row - column A
.Worksheets("Master").Range("A2").Offset(intRowCount, 0).Value = _
'create formulas linking to cells K750 to Q750 on each sheet
'starting at column B on the master sheet (uses n as column offset)
For n = 1 To 7
.Worksheets("Master").Range("A2").Offset(intRowCount, n).Formula = _
"='" & wsEach.Name & "'!" & Chr(Asc("K") + n - 1) & "750"
intRowCount = intRowCount + 1
If you just want a single total, this line replaces the For n - Next n loop
'create SUM() formula for cells K750:Q750 on each worksheet
.Worksheets("Master").Range("A2").Offset(intRowCount, 1).Formula = _
"=SUM('" & wsEach.Name & "'!K750:Q750)"
This places the following formula in cell B2 =SUM('1101'!K750:Q750) and so on for each worksheet
Note that the method of creating the column letters K to Q in the for - next loop will not work beyond Z. If at some time you extend your data beyond column Z, a different way of creating the column letters will be required, e.g., for column AA. In the SUM() version, just enter the cell address range.