how to take only the first sheet on every workbook and sum specific cells

[TheLSD]

Hi, I totally new at VBA Macro and I just got the task from my work to do some.
There are 2 commands that I need to solve:
1. I need to merge / copy as value only the first sheet of every workbook
2. The next thing is that I need to sum for specific cells of the worksheet, whenever I need to add some rows, it automatically sum the new row. I can use the sum across the sheets but it would take too much time to do so because there are approx. 500 rows.

Every worksheet, they have the same format and place.

I try to look upon the internet and found a code to merge the files but it merge all the sheets (I only need the first sheet only)
For the sum, I record the macro manually and it really takes too many times and when I try to insert a new row, I need to recode the macro
Here is the code that I found for merging the files into 1 workbook:

Sub Merge_Files()

Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual

Dim numberOfFilesChosen, i As Integer
Dim tempFileDialog As FileDialog
Dim mainWorkbook, sourceWorkbook As Workbook
Dim tempWorkSheet As Worksheet

Set mainWorkbook = Application.ActiveWorkbook
Set tempFileDialog = Application.FileDialog(msoFileDialogFilePicker)

tempFileDialog.AllowMultiSelect = True

numberOfFilesChosen = tempFileDialog.Show

For i = 1 To tempFileDialog.SelectedItems.Count

Workbooks.Open tempFileDialog.SelectedItems(i)

Set sourceWorkbook = ActiveWorkbook

For Each tempWorkSheet In sourceWorkbook.Worksheets
tempWorkSheet.Copy After:=mainWorkbook.Sheets(mainWorkbook.Worksheets.Count)
Next tempWorkSheet

sourceWorkbook.Close
Next i

For i = 1 To mainWorkbook.Worksheets.Count
mainWorkbook.Worksheets(i).Cells.Copy
mainWorkbook.Worksheets(i).Cells.PasteSpecial xlPasteValues
Next i

Application.ScreenUpdating = True
Application.Calculation = xlCalculationAutomatic

End Sub

I tweak it on the paste special values only because I only need the values only on the first sheet but it still takes all of the sheet inside the workbook

Any help would be appreciated. Thank you very much.

 

[bbrnx19]

Welcome, Frist part 1 for part 2 could you provide sample data using XL2BB

Sub Merge_Files()

Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual

Dim numberOfFilesChosen, i As Integer
Dim tempFileDialog As FileDialog
Dim mainWorkbook, sourceWorkbook As Workbook
Dim tempWorkSheet As Worksheet

Set mainWorkbook = Application.ActiveWorkbook
Set tempFileDialog = Application.FileDialog(msoFileDialogFilePicker)

tempFileDialog.AllowMultiSelect = True

numberOfFilesChosen = tempFileDialog.Show

For i = 1 To tempFileDialog.SelectedItems.Count

Workbooks.Open tempFileDialog.SelectedItems(i)

Set sourceWorkbook = ActiveWorkbook

'For Each tempWorkSheet In sourceWorkbook.Worksheets
'tempWorkSheet.Copy After:=mainWorkbook.Sheets(mainWorkbook.Worksheets.Count)
'Next tempWorkSheet

' replace with
sourceWorkbook.Worksheets(1).Copy After:=mainWorkbook.Sheets(mainWorkbook.Worksheets.Count)

sourceWorkbook.Close
Next i

For i = 1 To mainWorkbook.Worksheets.Count
mainWorkbook.Worksheets(i).Cells.Copy
mainWorkbook.Worksheets(i).Cells.PasteSpecial xlPasteValues
Next i

Application.ScreenUpdating = True
Application.Calculation = xlCalculationAutomatic

End Sub

 

[TheLSD]

Welcome, Frist part 1 for part 2 could you provide sample data using XL2BB

Sub Merge_Files()

Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual

Dim numberOfFilesChosen, i As Integer
Dim tempFileDialog As FileDialog
Dim mainWorkbook, sourceWorkbook As Workbook
Dim tempWorkSheet As Worksheet

Set mainWorkbook = Application.ActiveWorkbook
Set tempFileDialog = Application.FileDialog(msoFileDialogFilePicker)

tempFileDialog.AllowMultiSelect = True

numberOfFilesChosen = tempFileDialog.Show

For i = 1 To tempFileDialog.SelectedItems.Count

Workbooks.Open tempFileDialog.SelectedItems(i)

Set sourceWorkbook = ActiveWorkbook

'For Each tempWorkSheet In sourceWorkbook.Worksheets
'tempWorkSheet.Copy After:=mainWorkbook.Sheets(mainWorkbook.Worksheets.Count)
'Next tempWorkSheet

' replace with
sourceWorkbook.Worksheets(1).Copy After:=mainWorkbook.Sheets(mainWorkbook.Worksheets.Count)

sourceWorkbook.Close
Next i

For i = 1 To mainWorkbook.Worksheets.Count
mainWorkbook.Worksheets(i).Cells.Copy
mainWorkbook.Worksheets(i).Cells.PasteSpecial xlPasteValues
Next i

Application.ScreenUpdating = True
Application.Calculation = xlCalculationAutomatic

End Sub

WOW!! It works flawlessly!
Thank you so much!
For the next problem probably I need some time to re-arrange my messy data
I will post it soon
Again, thank you!

 

[TheLSD]

Welcome, Frist part 1 for part 2 could you provide sample data using XL2BB

Sub Merge_Files()

Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual

Dim numberOfFilesChosen, i As Integer
Dim tempFileDialog As FileDialog
Dim mainWorkbook, sourceWorkbook As Workbook
Dim tempWorkSheet As Worksheet

Set mainWorkbook = Application.ActiveWorkbook
Set tempFileDialog = Application.FileDialog(msoFileDialogFilePicker)

tempFileDialog.AllowMultiSelect = True

numberOfFilesChosen = tempFileDialog.Show

For i = 1 To tempFileDialog.SelectedItems.Count

Workbooks.Open tempFileDialog.SelectedItems(i)

Set sourceWorkbook = ActiveWorkbook

'For Each tempWorkSheet In sourceWorkbook.Worksheets
'tempWorkSheet.Copy After:=mainWorkbook.Sheets(mainWorkbook.Worksheets.Count)
'Next tempWorkSheet

' replace with
sourceWorkbook.Worksheets(1).Copy After:=mainWorkbook.Sheets(mainWorkbook.Worksheets.Count)

sourceWorkbook.Close
Next i

For i = 1 To mainWorkbook.Worksheets.Count
mainWorkbook.Worksheets(i).Cells.Copy
mainWorkbook.Worksheets(i).Cells.PasteSpecial xlPasteValues
Next i

Application.ScreenUpdating = True
Application.Calculation = xlCalculationAutomatic

End Sub

Test Macro Data (Autosaved).xlsm
	B	C	D	E	F	G	H	I
38	612300	OPS-Employee Welfare
39	612301	OPS-Cafetaria	123.035.000	1.600.400	2.934.600	6.700.000	6.200.000	7.700.000
40	612302	OPS-Recreation	-	-	-	-	-	-
41	612303	OPS-Uniform	24.000.000	2.000.000	2.000.000	2.000.000	2.000.000	2.000.000
42	612304	OPS-Marriage Allowance	18.000.000	1.500.000	1.500.000	1.500.000	1.500.000	1.500.000
43	612305	OPS-Home Rent Allowance	-	-	-	-	-	-
44	612306	OPS-Medical	-	-	-	-	-	-
45	612307	OPS-Other	-	-	-	-	-	-
46	612308	OPS-Hospital	36.000.000	3.000.000	3.000.000	3.000.000	3.000.000	3.000.000
47	612309	OPS-Sport Allowance	60.000.000	5.000.000	5.000.000	5.000.000	5.000.000	5.000.000
Master

Cell Formulas
Range	Formula
D39:D47	D39	=SUM(E39:P39)
E39:I47	E39	=SUM(HRGA:MSD!E39)

Sorry for the delay, perhaps this XL2BB would give you a clear picture of my problem.
So, the XL2BB is the Master Sheet of every sheet that sums from the different sheets in the same workbook with the same format.
For now, I create the macro basically by recording my self sum the cell one by one and copying it to the end of the sheet (and it takes some time too)
Is there any code that would shorten the manual code for every month and also the sum of it? Thank you so much

 

[TheLSD]

Welcome, Frist part 1 for part 2 could you provide sample data using XL2BB

Sub Merge_Files()

Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual

Dim numberOfFilesChosen, i As Integer
Dim tempFileDialog As FileDialog
Dim mainWorkbook, sourceWorkbook As Workbook
Dim tempWorkSheet As Worksheet

Set mainWorkbook = Application.ActiveWorkbook
Set tempFileDialog = Application.FileDialog(msoFileDialogFilePicker)

tempFileDialog.AllowMultiSelect = True

numberOfFilesChosen = tempFileDialog.Show

For i = 1 To tempFileDialog.SelectedItems.Count

Workbooks.Open tempFileDialog.SelectedItems(i)

Set sourceWorkbook = ActiveWorkbook

'For Each tempWorkSheet In sourceWorkbook.Worksheets
'tempWorkSheet.Copy After:=mainWorkbook.Sheets(mainWorkbook.Worksheets.Count)
'Next tempWorkSheet

' replace with
sourceWorkbook.Worksheets(1).Copy After:=mainWorkbook.Sheets(mainWorkbook.Worksheets.Count)

sourceWorkbook.Close
Next i

For i = 1 To mainWorkbook.Worksheets.Count
mainWorkbook.Worksheets(i).Cells.Copy
mainWorkbook.Worksheets(i).Cells.PasteSpecial xlPasteValues
Next i

Application.ScreenUpdating = True
Application.Calculation = xlCalculationAutomatic

End Sub

Sub All_Code()
'
' All_Code
'

'
    ActiveCell.FormulaR1C1 = "=SUM(HRGA:MSD!RC)"
    Range("E12").Select
    Selection.AutoFill Destination:=Range("E12:P12"), Type:=xlFillDefault
    Range("E12:P12").Select
    Selection.AutoFill Destination:=Range("E12:P26"), Type:=xlFillDefault
    Range("E12:P26").Select
    Range("D12").Select
    ActiveCell.FormulaR1C1 = "=SUM(RC[1]:RC[12])"
    Range("D12").Select
    Selection.AutoFill Destination:=Range("D12:D26"), Type:=xlFillDefault
    Range("D12:D26").Select
    Range("D28").Select
    ActiveCell.FormulaR1C1 = "=SUM(R[-16]C:R[-2]C)"
    Range("E28").Select
    ActiveCell.FormulaR1C1 = "=SUM(R[-16]C:R[-2]C)"
    Range("E28").Select
    Selection.AutoFill Destination:=Range("E28:P28"), Type:=xlFillDefault
    Range("E28:P28").Select

This is what I mean by doing the code manually. I also need to make sure that if the user increases a row, the code would recognize it and adapt it to the new format and sum it.
Again, sorry for the trouble and thank you very much.

 

[bbrnx19]

This may be way off what your looking for , I should of ask you for a data sheets example for HGRA:MSD before and after what your looking .

this is the code so far , see comments in code
1) Will find sheets dynamically between HGRA and MSD including HGRA and MSD
2) For each sheet from 1). find the last row of column E and add the Sum() formula onto the sheet
3) Master Sheet

Sub New_Sum()
Dim WB As Workbook
Dim StartSh, EndSh, i, LR As Integer

Set WB = ActiveWorkbook

' step throught each sheet to find sheet index number for a given name sheets.
' The start and end for a dynamic range of sheets, to capture the sheets between without
' noing the sheet names

For Each Sh In WB.Sheets
    ' Debug.Print statements are use to output terms to immediate window, see VB Menu>View
    ' not need in final code comment out
    Debug.Print Sh.Name
   
    If Sh.Name = "HRGA" Then StartSh = Sh.Index
    If Sh.Name = "MSD" Then EndSh = Sh.Index
Next

 Debug.Print "-----"
'used to step trought each Sheet from StartSh to EndSh
For i = StartSh To EndSh
    With WB.Worksheets(i)
   
    '<--- Here Some Code run with sheet i --->
       
        ' I'm now some what Guessing
        ' with out example data sheets before , after
        '
        ' for each data sheet, starting row and columns is E12 to P12
        ' Number of rows down may change, your code ends at E26 to P26
        ' column D = sum(E to P) to end rows
        ' end row +2  = sum(12 to end row) columns D to P
       
        ' code assuming column E is used to find last row of data
        ' checking for the first non blank cells bottom up
        ' ie if your data ends say E26 but cell E45 = " " then LR = 45
       
        LR = .Range("E:E").Cells.Find("*", , xlFormulas, xlWhole, xlByRows, xlPrevious).Row
       
        Debug.Print .Name; " "; LR ' sheet name; last row
       
        .Range(.Cells(12, "D"), .Cells(LR, "D")).Formula = "=sum(E12:P12)" ' Column D Formula
       
        .Range(.Cells(LR + 2, "D"), .Cells(LR + 2, "P")).Formula = "=sum(D12:D" & LR & ")" ' row LR +2 D:P
       
    End With
Next

With WB.Worksheets("Master")
'<--- Here Some Code run with sheet "Master" --->
    ' Master Sheet
    ' your XL2BB formula pointing to cells not used in your VB code
    ' with out example data sheets ?

End With

Set WB = Nothing

End Sub

 

[TheLSD]

This may be way off what your looking for , I should of ask you for a data sheets example for HGRA:MSD before and after what your looking .

this is the code so far , see comments in code
1) Will find sheets dynamically between HGRA and MSD including HGRA and MSD
2) For each sheet from 1). find the last row of column E and add the Sum() formula onto the sheet
3) Master Sheet

Sub New_Sum()
Dim WB As Workbook
Dim StartSh, EndSh, i, LR As Integer

Set WB = ActiveWorkbook

' step throught each sheet to find sheet index number for a given name sheets.
' The start and end for a dynamic range of sheets, to capture the sheets between without
' noing the sheet names

For Each Sh In WB.Sheets
    ' Debug.Print statements are use to output terms to immediate window, see VB Menu>View
    ' not need in final code comment out
    Debug.Print Sh.Name
  
    If Sh.Name = "HRGA" Then StartSh = Sh.Index
    If Sh.Name = "MSD" Then EndSh = Sh.Index
Next

 Debug.Print "-----"
'used to step trought each Sheet from StartSh to EndSh
For i = StartSh To EndSh
    With WB.Worksheets(i)
  
    '<--- Here Some Code run with sheet i --->
      
        ' I'm now some what Guessing
        ' with out example data sheets before , after
        '
        ' for each data sheet, starting row and columns is E12 to P12
        ' Number of rows down may change, your code ends at E26 to P26
        ' column D = sum(E to P) to end rows
        ' end row +2  = sum(12 to end row) columns D to P
      
        ' code assuming column E is used to find last row of data
        ' checking for the first non blank cells bottom up
        ' ie if your data ends say E26 but cell E45 = " " then LR = 45
      
        LR = .Range("E:E").Cells.Find("*", , xlFormulas, xlWhole, xlByRows, xlPrevious).Row
      
        Debug.Print .Name; " "; LR ' sheet name; last row
      
        .Range(.Cells(12, "D"), .Cells(LR, "D")).Formula = "=sum(E12:P12)" ' Column D Formula
      
        .Range(.Cells(LR + 2, "D"), .Cells(LR + 2, "P")).Formula = "=sum(D12:D" & LR & ")" ' row LR +2 D:P
      
    End With
Next

With WB.Worksheets("Master")
'<--- Here Some Code run with sheet "Master" --->
    ' Master Sheet
    ' your XL2BB formula pointing to cells not used in your VB code
    ' with out example data sheets ?

End With

Set WB = Nothing

End Sub

Test Macro Data (Autosaved).xlsm
	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P
6
7	COA	DESCRIPTION	YTD 2022	JAN	FEB	MARET	APR	MEI	JUNI	JULI	AGST	SEPT	OKT	NOV	DES
8
9	600000	Operational Cost
10	610000	OPS-Employee Compensation
11	612100	OPS-Employee Compensation
12	612101	OPS-Salaries	60.000.000	5.000.000	5.000.000	5.000.000	5.000.000	5.000.000	5.000.000	5.000.000	5.000.000	5.000.000	5.000.000	5.000.000	5.000.000
13	612102	OPS-Wages	12.000.000	1.000.000	1.000.000	1.000.000	1.000.000	1.000.000	1.000.000	1.000.000	1.000.000	1.000.000	1.000.000	1.000.000	1.000.000
14	612103	OPS-Transport	6.000.000	500.000	500.000	500.000	500.000	500.000	500.000	500.000	500.000	500.000	500.000	500.000	500.000
15	612104	OPS-Medical	3.000.000	250.000	250.000	250.000	250.000	250.000	250.000	250.000	250.000	250.000	250.000	250.000	250.000
16	612105	OPS-Hospital	1.200.000	100.000	100.000	100.000	100.000	100.000	100.000	100.000	100.000	100.000	100.000	100.000	100.000
17	612106	OPS-Incentive	-
18	612107	OPS-HP	1.200.000	100.000	100.000	100.000	100.000	100.000	100.000	100.000	100.000	100.000	100.000	100.000	100.000
19	612108	OPS-driver allowance	6.000.000	500.000	500.000	500.000	500.000	500.000	500.000	500.000	500.000	500.000	500.000	500.000	500.000
20	612109	OPS-Overtime	3.000.000	250.000	250.000	250.000	250.000	250.000	250.000	250.000	250.000	250.000	250.000	250.000	250.000
21	612110	OPS-Bonus	12.000.000	1.000.000	1.000.000	1.000.000	1.000.000	1.000.000	1.000.000	1.000.000	1.000.000	1.000.000	1.000.000	1.000.000	1.000.000
22	612111	OPS-Consumption Allowance	-
23	612112	OPS-Period of Employee Benefits	-
24	612113	OPS-Severance	-
25	612114	OPS-Glasess Allowance	-
26	612199	OPS-Other Allowance	-
27			-
28		SUB TOTAL EMPLOYEE COMPENSATION *	104.400.000	8.700.000	8.700.000	8.700.000	8.700.000	8.700.000	8.700.000	8.700.000	8.700.000	8.700.000	8.700.000	8.700.000	8.700.000
OPEX Dummy

This is what the HRGA and MSD look like
They have similar form, the difference is that the value inside the table only
I use HRGA:MSD as the Start and End to make if the user add a new sheet, he/she will just put between them
My other concern is that if he/she decide to add a new row, my manually input macro won't stand chance for it and I need to re-record it all the way around
This would also won't do good for the sum macro for every segment.
Hope this form would give you a clear vision of my problem.

 

[bbrnx19]

$sum.xlsm
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q
1		COA	DESCRIPTION	YTD 2022	JAN	FEB	MARET	APR	MEI	JUNI	JULI	AGST	SEPT	OKT	NOV	DES
2		612100	OPS-Employee Compensation	$ -	$ -	$ -	$ -	$ -	$ -	$ -	$ -	$ -	$ -	$ -	$ -	$ -
3		612101	OPS-Salaries	$ 299,934,000.00	$ 25,000,000.00	$ 24,999,000.00	$ 24,998,000.00	$ 24,997,000.00	$ 24,996,000.00	$ 24,995,000.00	$ 24,994,000.00	$ 24,993,000.00	$ 24,992,000.00	$ 24,991,000.00	$ 24,990,000.00	$ 24,989,000.00
4		612102	OPS-Wages	$ 59,934,000.00	$ 5,000,000.00	$ 4,999,000.00	$ 4,998,000.00	$ 4,997,000.00	$ 4,996,000.00	$ 4,995,000.00	$ 4,994,000.00	$ 4,993,000.00	$ 4,992,000.00	$ 4,991,000.00	$ 4,990,000.00	$ 4,989,000.00
5		612103	OPS-Transport	$ 29,934,000.00	$ 2,500,000.00	$ 2,499,000.00	$ 2,498,000.00	$ 2,497,000.00	$ 2,496,000.00	$ 2,495,000.00	$ 2,494,000.00	$ 2,493,000.00	$ 2,492,000.00	$ 2,491,000.00	$ 2,490,000.00	$ 2,489,000.00
6		612104	OPS-Medical	$ 14,934,000.00	$ 1,250,000.00	$ 1,249,000.00	$ 1,248,000.00	$ 1,247,000.00	$ 1,246,000.00	$ 1,245,000.00	$ 1,244,000.00	$ 1,243,000.00	$ 1,242,000.00	$ 1,241,000.00	$ 1,240,000.00	$ 1,239,000.00
7		612105	OPS-Hospital	$ 5,934,000.00	$ 500,000.00	$ 499,000.00	$ 498,000.00	$ 497,000.00	$ 496,000.00	$ 495,000.00	$ 494,000.00	$ 493,000.00	$ 492,000.00	$ 491,000.00	$ 490,000.00	$ 489,000.00
8		612106	OPS-Incentive	$ -65,974.00	$ 15.00	$ -999.00	$ -1,999.00	$ -2,999.00	$ -3,999.00	$ -4,999.00	$ -5,999.00	$ -6,999.00	$ -7,999.00	$ -8,999.00	$ -9,999.00	$ -10,999.00
9		612107	OPS-HP	$ 5,934,000.00	$ 500,000.00	$ 499,000.00	$ 498,000.00	$ 497,000.00	$ 496,000.00	$ 495,000.00	$ 494,000.00	$ 493,000.00	$ 492,000.00	$ 491,000.00	$ 490,000.00	$ 489,000.00
10		612108	OPS-driver allowance	$ 29,934,000.00	$ 2,500,000.00	$ 2,499,000.00	$ 2,498,000.00	$ 2,497,000.00	$ 2,496,000.00	$ 2,495,000.00	$ 2,494,000.00	$ 2,493,000.00	$ 2,492,000.00	$ 2,491,000.00	$ 2,490,000.00	$ 2,489,000.00
11		612109	OPS-Overtime	$ 14,934,000.00	$ 1,250,000.00	$ 1,249,000.00	$ 1,248,000.00	$ 1,247,000.00	$ 1,246,000.00	$ 1,245,000.00	$ 1,244,000.00	$ 1,243,000.00	$ 1,242,000.00	$ 1,241,000.00	$ 1,240,000.00	$ 1,239,000.00
12		612110	OPS-Bonus	$ 59,934,000.00	$ 5,000,000.00	$ 4,999,000.00	$ 4,998,000.00	$ 4,997,000.00	$ 4,996,000.00	$ 4,995,000.00	$ 4,994,000.00	$ 4,993,000.00	$ 4,992,000.00	$ 4,991,000.00	$ 4,990,000.00	$ 4,989,000.00
13		612111	OPS-Consumption Allowance	$ -	$ -	$ -	$ -	$ -	$ -	$ -	$ -	$ -	$ -	$ -	$ -	$ -
14		612112	OPS-Period of Employee Benefits	$ -	$ -	$ -	$ -	$ -	$ -	$ -	$ -	$ -	$ -	$ -	$ -	$ -
15		612113	OPS-Severance	$ -	$ -	$ -	$ -	$ -	$ -	$ -	$ -	$ -	$ -	$ -	$ -	$ -
16		612114	OPS-Glasess Allowance	$ -	$ -	$ -	$ -	$ -	$ -	$ -	$ -	$ -	$ -	$ -	$ -	$ -
17		612199	OPS-Other Allowance	$ -	$ -	$ -	$ -	$ -	$ -	$ -	$ -	$ -	$ -	$ -	$ -	$ -
18				$ 521,340,026.00	$ 43,500,015.00	$ 43,490,001.00	$ 43,480,001.00	$ 43,470,001.00	$ 43,460,001.00	$ 43,450,001.00	$ 43,440,001.00	$ 43,430,001.00	$ 43,420,001.00	$ 43,410,001.00	$ 43,400,001.00	$ 43,390,001.00
19
Master

Cell Formulas
Range	Formula
D2:P17	D2	=Sum_TheLSD(HRGA!$C:$C,Master!$C2,MSD!D:D)
D18:P18	D18	=SUM(D2:D16)

1) Changed to find first row = "DESCR" in cell and the last row ="SUB TOTAL" in cell for data sheets if additional rows are inserted between will be captured
2) master sheet find last row and add formula
3) Created UDF = user defined formula 'sum_TheLSD' which can sumif across multiple sheets in a limited fashion, but to your needs , add vba code below first

Sum_TheLSD(HRGA!$C:$C,Master!$C2,MSD!D:D)

Sub New_Sum()
Dim WB As Workbook
Dim StartSh, EndSh, i, j, LR, FR As Integer

Set WB = ActiveWorkbook

' step throught each sheet to find sheet index number for a given name sheets.
' The start and end for a dynamic range for sheets, to capture the sheets between without
' noting the sheet names

StartSh = WB.Sheets("HRGA").Index
EndSh = WB.Sheets("MSD").Index

 Debug.Print "-----"
'used to step trought each Sheet from StartSh to EndSh
ReDim FRLR(0 To EndSh - StartSh, 2)
j = 0
For i = StartSh To EndSh
     
    '<--- Here Some Code run with sheet i --->
       
        ' I'm now some what Guessing
        ' with out example data sheets before , after
        '
     
        FR = WB.Worksheets(i).Cells.Find(What:="DESCR", After:=WB.Worksheets(i).Cells(1, 1), LookIn:=xlValues, LookAt:= _
        xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False _
        , SearchFormat:=False).Row
      
        LR = WB.Worksheets(i).Cells.Find(What:="SUB TOTAL", After:=WB.Worksheets(i).Cells(1, 1), LookIn:=xlValues, LookAt:= _
        xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False _
        , SearchFormat:=False).Row
       
        Debug.Print WB.Worksheets(i).Name; " "; FR; " "; LR ' sheet name; First Row last row
       
        WB.Worksheets(i).Range(WB.Worksheets(i).Cells(FR + 2, "D"), WB.Worksheets(i).Cells(LR - 2, "D")).Formula = "=sum(E" & FR + 2 & ":P" & FR + 2 & ")" ' Column D Formula
       
        WB.Worksheets(i).Range(WB.Worksheets(i).Cells(LR, "D"), WB.Worksheets(i).Cells(LR, "P")).Formula = "=sum(D" & FR + 2 & ":D" & LR - 1 & ")"
     
    FRLR(j, 0) = WB.Worksheets(i).Name
    FRLR(j, 1) = FR
    FRLR(j, 2) = LR
    j = j + 1
Next

With WB.Worksheets("Master")
'<--- Here Some Code run with sheet "Master" --->
    ' Master Sheet
    ' your XL2BB formula pointing to cells not used in your VB code
    ' with out example data sheets ?
    LR = WB.Worksheets("Master").Cells.Find(What:="*", After:=WB.Worksheets("Master").Cells(1, 3), LookIn:=xlValues, LookAt:= _
        xlPart, SearchOrder:=xlByColumns, SearchDirection:=xlPrevious, MatchCase:=False _
        , SearchFormat:=False).Row
   
   .Range("d2:p" & LR).Formula = "=Sum_TheLSD(HRGA!$C:$C,Master!$C2,MSD!D:D)" ' uses UDF Sum_TheLSD to sumif across sheets using master C
  
   .Range("D" & LR + 1 & ":P" & LR + 1).Formula = "=sum(d2:d" & LR - 1 & ")"
   

End With
Set WB = Nothing

End Sub

Function Sum_TheLSD(FirstShCriteria_Range, Criteria_Cell, LastShSum_range)

' eg =Sum_TheLSD(HRGA!$C:$C,Master!$C2,MSD!D:D)
' you can use this in formula bar

Set FS = FirstShCriteria_Range ' is the criteta lookup range on the fristsh
Set LS = LastShSum_range 'is the sum range on the lastsh
f = 0
For i = FS.Worksheet.Index To LS.Worksheet.Index
Debug.Print i
    f = f + WorksheetFunction.SumIf(Sheets(i).Columns(FS.Column), _
    Criteria_Cell.Value, Sheets(i).Columns(LS.Column))
 Next
Sum_TheLSD = f
Set FS = Nothing
Set LS = Nothing

End Function

 

[TheLSD]

$sum.xlsm
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q
1		COA	DESCRIPTION	YTD 2022	JAN	FEB	MARET	APR	MEI	JUNI	JULI	AGST	SEPT	OKT	NOV	DES
2		612100	OPS-Employee Compensation	$ -	$ -	$ -	$ -	$ -	$ -	$ -	$ -	$ -	$ -	$ -	$ -	$ -
3		612101	OPS-Salaries	$ 299,934,000.00	$ 25,000,000.00	$ 24,999,000.00	$ 24,998,000.00	$ 24,997,000.00	$ 24,996,000.00	$ 24,995,000.00	$ 24,994,000.00	$ 24,993,000.00	$ 24,992,000.00	$ 24,991,000.00	$ 24,990,000.00	$ 24,989,000.00
4		612102	OPS-Wages	$ 59,934,000.00	$ 5,000,000.00	$ 4,999,000.00	$ 4,998,000.00	$ 4,997,000.00	$ 4,996,000.00	$ 4,995,000.00	$ 4,994,000.00	$ 4,993,000.00	$ 4,992,000.00	$ 4,991,000.00	$ 4,990,000.00	$ 4,989,000.00
5		612103	OPS-Transport	$ 29,934,000.00	$ 2,500,000.00	$ 2,499,000.00	$ 2,498,000.00	$ 2,497,000.00	$ 2,496,000.00	$ 2,495,000.00	$ 2,494,000.00	$ 2,493,000.00	$ 2,492,000.00	$ 2,491,000.00	$ 2,490,000.00	$ 2,489,000.00
6		612104	OPS-Medical	$ 14,934,000.00	$ 1,250,000.00	$ 1,249,000.00	$ 1,248,000.00	$ 1,247,000.00	$ 1,246,000.00	$ 1,245,000.00	$ 1,244,000.00	$ 1,243,000.00	$ 1,242,000.00	$ 1,241,000.00	$ 1,240,000.00	$ 1,239,000.00
7		612105	OPS-Hospital	$ 5,934,000.00	$ 500,000.00	$ 499,000.00	$ 498,000.00	$ 497,000.00	$ 496,000.00	$ 495,000.00	$ 494,000.00	$ 493,000.00	$ 492,000.00	$ 491,000.00	$ 490,000.00	$ 489,000.00
8		612106	OPS-Incentive	$ -65,974.00	$ 15.00	$ -999.00	$ -1,999.00	$ -2,999.00	$ -3,999.00	$ -4,999.00	$ -5,999.00	$ -6,999.00	$ -7,999.00	$ -8,999.00	$ -9,999.00	$ -10,999.00
9		612107	OPS-HP	$ 5,934,000.00	$ 500,000.00	$ 499,000.00	$ 498,000.00	$ 497,000.00	$ 496,000.00	$ 495,000.00	$ 494,000.00	$ 493,000.00	$ 492,000.00	$ 491,000.00	$ 490,000.00	$ 489,000.00
10		612108	OPS-driver allowance	$ 29,934,000.00	$ 2,500,000.00	$ 2,499,000.00	$ 2,498,000.00	$ 2,497,000.00	$ 2,496,000.00	$ 2,495,000.00	$ 2,494,000.00	$ 2,493,000.00	$ 2,492,000.00	$ 2,491,000.00	$ 2,490,000.00	$ 2,489,000.00
11		612109	OPS-Overtime	$ 14,934,000.00	$ 1,250,000.00	$ 1,249,000.00	$ 1,248,000.00	$ 1,247,000.00	$ 1,246,000.00	$ 1,245,000.00	$ 1,244,000.00	$ 1,243,000.00	$ 1,242,000.00	$ 1,241,000.00	$ 1,240,000.00	$ 1,239,000.00
12		612110	OPS-Bonus	$ 59,934,000.00	$ 5,000,000.00	$ 4,999,000.00	$ 4,998,000.00	$ 4,997,000.00	$ 4,996,000.00	$ 4,995,000.00	$ 4,994,000.00	$ 4,993,000.00	$ 4,992,000.00	$ 4,991,000.00	$ 4,990,000.00	$ 4,989,000.00
13		612111	OPS-Consumption Allowance	$ -	$ -	$ -	$ -	$ -	$ -	$ -	$ -	$ -	$ -	$ -	$ -	$ -
14		612112	OPS-Period of Employee Benefits	$ -	$ -	$ -	$ -	$ -	$ -	$ -	$ -	$ -	$ -	$ -	$ -	$ -
15		612113	OPS-Severance	$ -	$ -	$ -	$ -	$ -	$ -	$ -	$ -	$ -	$ -	$ -	$ -	$ -
16		612114	OPS-Glasess Allowance	$ -	$ -	$ -	$ -	$ -	$ -	$ -	$ -	$ -	$ -	$ -	$ -	$ -
17		612199	OPS-Other Allowance	$ -	$ -	$ -	$ -	$ -	$ -	$ -	$ -	$ -	$ -	$ -	$ -	$ -
18				$ 521,340,026.00	$ 43,500,015.00	$ 43,490,001.00	$ 43,480,001.00	$ 43,470,001.00	$ 43,460,001.00	$ 43,450,001.00	$ 43,440,001.00	$ 43,430,001.00	$ 43,420,001.00	$ 43,410,001.00	$ 43,400,001.00	$ 43,390,001.00
19
Master

Cell Formulas
Range	Formula
D2:P17	D2	=Sum_TheLSD(HRGA!$C:$C,Master!$C2,MSD!D:D)
D18:P18	D18	=SUM(D2:D16)

1) Changed to find first row = "DESCR" in cell and the last row ="SUB TOTAL" in cell for data sheets if additional rows are inserted between will be captured
2) master sheet find last row and add formula
3) Created UDF = user defined formula 'sum_TheLSD' which can sumif across multiple sheets in a limited fashion, but to your needs , add vba code below first

Sum_TheLSD(HRGA!$C:$C,Master!$C2,MSD!D:D)

Sub New_Sum()
Dim WB As Workbook
Dim StartSh, EndSh, i, j, LR, FR As Integer

Set WB = ActiveWorkbook

' step throught each sheet to find sheet index number for a given name sheets.
' The start and end for a dynamic range for sheets, to capture the sheets between without
' noting the sheet names

StartSh = WB.Sheets("HRGA").Index
EndSh = WB.Sheets("MSD").Index

 Debug.Print "-----"
'used to step trought each Sheet from StartSh to EndSh
ReDim FRLR(0 To EndSh - StartSh, 2)
j = 0
For i = StartSh To EndSh
    
    '<--- Here Some Code run with sheet i --->
      
        ' I'm now some what Guessing
        ' with out example data sheets before , after
        '
    
        FR = WB.Worksheets(i).Cells.Find(What:="DESCR", After:=WB.Worksheets(i).Cells(1, 1), LookIn:=xlValues, LookAt:= _
        xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False _
        , SearchFormat:=False).Row
     
        LR = WB.Worksheets(i).Cells.Find(What:="SUB TOTAL", After:=WB.Worksheets(i).Cells(1, 1), LookIn:=xlValues, LookAt:= _
        xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False _
        , SearchFormat:=False).Row
      
        Debug.Print WB.Worksheets(i).Name; " "; FR; " "; LR ' sheet name; First Row last row
      
        WB.Worksheets(i).Range(WB.Worksheets(i).Cells(FR + 2, "D"), WB.Worksheets(i).Cells(LR - 2, "D")).Formula = "=sum(E" & FR + 2 & ":P" & FR + 2 & ")" ' Column D Formula
      
        WB.Worksheets(i).Range(WB.Worksheets(i).Cells(LR, "D"), WB.Worksheets(i).Cells(LR, "P")).Formula = "=sum(D" & FR + 2 & ":D" & LR - 1 & ")"
    
    FRLR(j, 0) = WB.Worksheets(i).Name
    FRLR(j, 1) = FR
    FRLR(j, 2) = LR
    j = j + 1
Next

With WB.Worksheets("Master")
'<--- Here Some Code run with sheet "Master" --->
    ' Master Sheet
    ' your XL2BB formula pointing to cells not used in your VB code
    ' with out example data sheets ?
    LR = WB.Worksheets("Master").Cells.Find(What:="*", After:=WB.Worksheets("Master").Cells(1, 3), LookIn:=xlValues, LookAt:= _
        xlPart, SearchOrder:=xlByColumns, SearchDirection:=xlPrevious, MatchCase:=False _
        , SearchFormat:=False).Row
  
   .Range("d2:p" & LR).Formula = "=Sum_TheLSD(HRGA!$C:$C,Master!$C2,MSD!D:D)" ' uses UDF Sum_TheLSD to sumif across sheets using master C
 
   .Range("D" & LR + 1 & ":P" & LR + 1).Formula = "=sum(d2:d" & LR - 1 & ")"
  

End With
Set WB = Nothing

End Sub

Function Sum_TheLSD(FirstShCriteria_Range, Criteria_Cell, LastShSum_range)

' eg =Sum_TheLSD(HRGA!$C:$C,Master!$C2,MSD!D:D)
' you can use this in formula bar

Set FS = FirstShCriteria_Range ' is the criteta lookup range on the fristsh
Set LS = LastShSum_range 'is the sum range on the lastsh
f = 0
For i = FS.Worksheet.Index To LS.Worksheet.Index
Debug.Print i
    f = f + WorksheetFunction.SumIf(Sheets(i).Columns(FS.Column), _
    Criteria_Cell.Value, Sheets(i).Columns(LS.Column))
 Next
Sum_TheLSD = f
Set FS = Nothing
Set LS = Nothing

End Function

Thank you for the code!!