<div>
If you are using Excel 2007 or 2010, I would simple create Tables for your various worksheets, and then in Table Tools/Design ribbon tab, select Total Row in the Table Style Options to force a grand total row.<br />
<br />
Then, on another worksheet, use formulas to pull in the grand totals, such as:<br />
<br />
=SUM(Table1[MyValueColumnN<wbr />ame])<br />
=SUM(Table2[MyValueColumnN<wbr />ame])<br />
=SUM(Table3[MyValueColumnN<wbr />ame])<br />
etc.<br />
<br />
If you have the name of the table in, say, Col A, you can use:<br />
<br />
=SUM(INDIRECT(A1&amp;&quot;[MyValue<wbr />ColumnName<wbr />]&quot;))
</div>


If you are using Excel 2007 or 2010, I would simple create Tables for your various worksheets, and then in Table Tools/Design ribbon tab, select Total Row in the Table Style Options to force a grand total row.

Then, on another worksheet, use formulas to pull in the grand totals, such as:

=SUM(Table1[MyValueColumnName])
=SUM(Table2[MyValueColumnName])
=SUM(Table3[MyValueColumnName])
etc.

If you have the name of the table in, say, Col A, you can use:

=SUM(INDIRECT(A1&"[MyValueColumnName]"))

summary.JPG

SheetTally.xlsm

<div>
<div class="content wysiwyg-content">
I have Excel workbooks that have a variable number of sheets. I would like to sum the same column in each sheet, and create a table (in a new sheet) that has the sheet name, total and number of values in 3 columns. There is no other numeric data in these columns 
</div>
</div>


I have Excel workbooks that have a variable number of sheets. I would like to sum the same column in each sheet, and create a table (in a new sheet) that has the sheet name, total and number of values in 3 columns. There is no other numeric data in these columns 

how  to create Totals for the same column for all sheets in an Excel workbook. VBA

Microsoft Excel topics include formulas, formatting, VBA macros and user-defined functions, and everything else related to the spreadsheet user interface, including error messages.