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]

if you want a macro for naming the sheet the below will work fine

Sub name_shts()
Dim sh As Worksheet
For Each sh In ThisWorkbook.Worksheets
ActiveCell.Value = sh.Name
ActiveCell.Offset(1, 0).Activate
Next sh
End Sub

 

[BarryL]

it will list the sheets and then you can just add the named range.

if you want a macro for naming the sheet the below will work fine

Sub name_shts()
Dim sh As Worksheet
For Each sh In ThisWorkbook.Worksheets
ActiveCell.Value = sh.Name
ActiveCell.Offset(1, 0).Activate
Next sh
End Sub

 

[Rick Rothstein]

it will list the sheets and then you can just add the named range.

Before the OP does that, he may want to look at Message #10.

 

[Weeble]

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

Thx Rick! How do I make it paste to a location of my choice ? Sorry I am new to VBA

 

[Rick Rothstein]

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

Thx Rick! How do I make it paste to a location of my choice ? Sorry I am new to VBA

I had hoped my "HOW TO INSTALL UDFs" would have made that clear. After installing the code, simply put this formula in whatever cell you want the total to show in...

=TotalColT()

 

[BarryL]

Before the OP does that, he may want to look at Message #10.

Nice, definitely less hassle. Maybe he might want it to be volatile.

Weeble, If that the case just add Application.Volatile in at the start.

 

[Weeble]

Rick , Sorry I missed it ! But wow, that worked above my expectations, thx alot for this.

Thx BarryL for clearing it up aswell

 

[pgc01]

Hi

You can also use a 3D range, like:

=SUM(Firstsheet:Lastsheet!T:T)

 

[Rick Rothstein]

You can also use a 3D range, like:

=SUM(Firstsheet:Lastsheet!T:T)

A much better solution (provided you can trust your users not to rearrange the sheet tabs on you:wink.

 

[pgc01]

A much better solution (provided you can trust your users not to rearrange the sheet tabs on you:wink.

You're right.

I use the usual solution of adding one worksheet before and one after the group of worksheets in the formula. I name them Start and End and then use

=SUM(Start:End!T:T)

This way I can insert, delete or move worksheets as long and they remain between Start and End and the formula will always work.