VBA Sum all worsksheets in column T

[Weeble]

Heya !

I have a pretty big excel file with aprox 60 worksheets. In every sheet column T has aprox 1000-4000 rows of data.
How would be the easiest way to summarise this data into a "summary-sheet" ?
There is alot of blank spaces in column T aswell if that might be an issue?

 

[BarryL]

what are the worksheet names? are they a sequence or completely random?

 

[Weeble]

They are quite random. It's 52 weeks, with name of months and such.

 

[BarryL]

so you'd like a summary sheet with each sheet name and a value for column T beside it?

 

[Weeble]

The only thing I need is the total sum of all sheets in one cell. No need for sheet name.

 

[BarryL]

Make a list of the worksheet names in a new sheet lets say from A1:A60 then put the below in A1 and drag down.

=SUM(INDIRECT("'"&A2&"'!T:T"))

 

[BarryL]

The only thing I need is the total sum of all sheets in one cell. No need for sheet name.

right youll need a named range with all your sheet names then and the use sum with sumproduct

 

[BarryL]

create a list f all of your sheets. Then add them to a named range , Lets call it SHTS. then use the below formula

=SUMPRODUCT(SUM(INDIRECT("'"&SHTS&"'!T:T")))

 

[Weeble]

I think this will work fine Thx for your help BarryL!

 

[Rick Rothstein]

The only thing I need is the total sum of all sheets in one cell. No need for sheet name.

Here is a UDF (user defined function) that should give you that total...

Function TotalColT() As Double
  Dim WS As Worksheet
  For Each WS In ThisWorkbook.Worksheets
    TotalColT = TotalColT + Application.Sum(WS.Range("T:T"))
  Next
End Function

HOW TO INSTALL UDFs
------------------------------------
If you are new to UDFs, they are easy to install and use. To install it, simply press ALT+F11 to go into the VB editor and, once there, click Insert/Module on its menu bar, then copy/paste the above code into the code window that just opened up. That's it.... you are done. You can now use TotalColT just like it was a built-in Excel function. For example,

=TotalColT()

If you are using XL2007 or above, make sure you save your file as an "Excel Macro-Enabled Workbook (*.xlsm) and answer the "do you want to enable macros" question as "yes" or "OK" (depending on the button label for your version of Excel) the next time you open your workbook.