Sum col's in multiple worksheets, list totals 1

[TrentSteel]

Hello all

I have what I think would be a fairly simple problem, but time is of the essence. I have 6 workbooks with anywhere between 15-50 worksheets per book. Column B across all worksheets and workbooks displays file size in bites. I need to sum all Col B’s in the various sheets, within each workbook, and display the totals in a list with the worksheet title that they came from. The eventual outcome will be a Top 10 sort of list. Can anyone help me with a short-cut to sum the column and display with worksheet name, in a separate worksheet?

TIA

 

[SkipVought]

Hi,

Not with built-in function.

However, VBA - YES!

Open all your workbooks

Sub SumBytes()
    Dim wsTotals As Worksheet, lRow As Long
    Set wsTotals = Worksheets.Add
    lRow = 1
    With wsTotals
      .Cells(lRow, 1).Value = "Workbook Name"
      .Cells(lRow, 2).Value = "Worksheet Name"
      .Cells(lRow, 3).Value = "Bytes"
    End With
    
    For Each wb In Workbooks
      For Each ws In wb.Worksheets
        With wsTotals
            .Cells(lRow, 1).Value = wb.Name
            .Cells(lRow, 2).Value = ws.Name
            .Cells(lRow, 3).Value = Application.Sum(ws.Range("B:B"))
        End With
        lRow = lRow + 1
     Next
    Next

End Sub

Hope this helps

Skip,
Skip@TheOfficeExperts.com

http://www.TheOfficeExperts.com

 

[TrentSteel]

Skip

Thanks a million, worked perfect!!!

TrentSteel

 

[SkipVought]

Clark Trent -- man of steel??? Ha!

Skip,
Skip@TheOfficeExperts.com

http://www.TheOfficeExperts.com

 

[TrentSteel]

Skip

Still working with the same sheets, books, records etc...the fourth column is a date modified column. How would I be able to pull individual records based on date last modified, and display them. A little more deatil on the format of my worksheets....

Colm A - File name; eg Folder\Folder\Folder\File.txt
Colm B - File Size; eg 64000
Colm C - Date Last Accessed; eg 1/3/2003
Colm D - Date Last Modified; eg 1/2/2003
Colm E - Date Created; eg 1/1/2003

So, I would like to pull all records, from all open workbooks that have a date last modified older than 1/1/1999, display in another sheet and total the size. Can you give me some pointers here?

TIA

 

[SkipVought]

Sub SumBytes()
    Dim wsTotals As Worksheet, lRow As Long
    Set wsTotals = Worksheets.Add
    lRow = 1
    With wsTotals
      .Cells(lRow, 1).Value = "Workbook Name"
      .Cells(lRow, 2).Value = "Worksheet Name"
      .Cells(lRow, 3).Value = "Bytes"
    End With
    
    For Each wb In Workbooks
      For Each ws In wb.Worksheets
        With wsTotals
            If Application.MAX(ws.Range(&quot;D:D&quot;)) < #1/1/1999# then
              .Cells(lRow, 1).Value = wb.Name
              .Cells(lRow, 2).Value = ws.Name
              .Cells(lRow, 3).Value = Application.Sum(ws.Range(&quot;B:B&quot;))
              .Cells(lRow, 4).Value = Application.MAX(ws.Range(&quot;D:D&quot;))
             End If
        End With
        lRow = lRow + 1
     Next
    Next

End Sub

might work

Skip,
Skip@TheOfficeExperts.com

http://www.TheOfficeExperts.com

 

[SkipVought]

OOPS

Put the row incriment inside IF...End If



Skip,
Skip@TheOfficeExperts.com

http://www.TheOfficeExperts.com

 

[xlbo]

TrentSteel - think that this is both helpful ANd expert and therefore should be deservi ng of a star.....

Rgds, Geoff
Quantum materiae materietur marmota monax si marmota monax materiam possit materiari?
Want the best answers to your questions ? faq222-2244