Sum Data From a Dynamic Range of A Column On Another Worksheet

Formula:

Please Login or Register  to view this content.

Renamed data sheets as Data1, Data2 purely for illustration so just change to first/last sheet names (must be consecutive)

And ensure descriptions in column A match your headers

IF you use same title name, OFFSET or INDIRECT function could help.
(I modified title text in SourceData for matching)
SUBSTITUTE function is used for matching titles, and if B3 cell has Source_Sheet_Name,
try this formula.
B4 =SUM(OFFSET(INDIRECT("'"&$B$3&"'!$A$1"),0, MATCH(SUBSTITUTE(SUBSTITUTE($A4,"Total ",""),":",""),INDIRECT("'"&$B$3&"'!$A$1:$Z$1"),0)-1, COUNTA(INDIRECT("'"&$B$3&"'!$A:$A"))) )

If you must need VBA code, please let me know.

스크린샷 2024-10-14 172155.png

What "automation" is required ? as the formulae will retrieve the results automatically - no change required other than to make the range your likely maximum.

One time set-up.

Attached code simply applies the formula given earlier. There are 2 sheets(First/Last) which are a prefix/suffix to your data sheets

Please Login or Register  to view this content.

If the data range varies from sheet to sheet then you need to process each sheet individually ( a loop of some kind) or simply make the range the likely maximum if you want a simple formula as provided.

If you change "Total Expenses" in SourceData sheet to "Expenses" and "Mileage Activity Totals" to "Mileage Activity" so that your consistent with the entries in Summary sheet.
The text in Column A in Summary Sheet should match the text you're comparing in Row1 in SourceData Sheet with Total and a space ("Total ")in front of it and the ":" following it.

Please Login or Register  to view this content.

Building on Jolivanes' code: assumes all sheets EXCEPT "Summary" are to be processed ..

Please Login or Register  to view this content.

Commented the code ...

Please Login or Register  to view this content.