automatic column summation in Excel 07

[wwwups]

I am a newbie with little VBA/macro programming experience. I am currently handling an Excel file with multiple spreadsheets where each sheet has a variable column length (i.e. Sheet1 may be 20 rows, Sheet2 may be 40 rows, etc.).

Each worksheet (except the first two) has data in columns I to BD, and some of this data may end with zeros. The first column (A) however, always has a non-zero number (for xlUp functionality). The length of the column obtained from the length of column A may be used for specifying the end-cell for summation.

My task is to calculate the sum of values in columns I to BD of each worksheet except the first two. These are to be reported a in a line below the actual data (i.e. for column I, after the last cell, there is a blank cell succeeded by a cell corresponding to the sum of values I2 to the last cell (corresponding to the last cell in column A).

I am doing this for over 50 worksheets, and therefore would appreciate the convenience of having this in a VBA script/macro. Could anyone please provide any suggestions?
Thanks,

 

[VoG]

Example for column I on the active sheet

Sub Suum()
Dim LR As Long
LR = Range("A" & Rows.Count).End(xlUp).Row
Range("I" & LR + 2).Value = WorksheetFunction.Sum(Range("I1:I" & LR))
End Sub

 

[wwwups]

Example for column I on the active sheet

Sub Suum()
Dim LR As Long
LR = Range("A" & Rows.Count).End(xlUp).Row
Range("I" & LR + 2).Value = WorksheetFunction.Sum(Range("I1:I" & LR))
End Sub

Thanks VoG! I modified this slightly, but it generally works. How do I apply it to all or selected worksheets in the document? (I want to skip the first two spreadsheets)?

Also, to provide you some context, I have another script that was also obtained from the other forums, this script writes categories of a certain type (for example, if my data is about fruits and they are sorted by the type of fruit - oranges, apples, etc., this script takes all the rows containing orange, and puts them in the "oranges" worksheet. Is there a way I could integrate the above script with that provided below (i.e. distribute entries to each worksheet, and sum columns I to BD of each worksheet)?

Or is it is much easier to have two scripts, one for populating each worksheet, and the other for summing columns I to BD of selected worksheets?
-Thanks!

p.s.

Public Sub TypeAssign()

Dim lngDestRow As Long
Dim strDestSht As String

For i = 2 To Sheets("Sheet1").Cells(Rows.Count, 1).End(xlUp).Row
    strDestSht = Sheets("Sheet1").Cells(i, "G").Value
    lngDestRow = Sheets(strDestSht).Cells(Rows.Count, 1).End(xlUp).Row + 1
    Sheets("Sheet1").Rows(i).Copy Destination:=Sheets(Sheets("Sheet1").Cells(i, "G").Value).Cells(lngDestRow, 1)
Next i

End Sub

 

[VoG]

I would stick to separate subs.

To expand on my previous answer, how about

Sub Suum()
Dim LR As Long, i As Long, j As Long
For i = 3 To Worksheets.Count
    With Worksheets(i)
        LR = .Cells(Rows.Count, 1).End(xlUp).Row
        For j = 9 To 56
            .Cells(LR + 2, j).Value = WorksheetFunction.Sum(.Range(.Cells(1, j), .Cells(LR, j)))
        Next j
    End With
Next i
End Sub

 

[wwwups]

I would stick to separate subs.

To expand on my previous answer, how about

Sub Suum()
Dim LR As Long, i As Long, j As Long
For i = 3 To Worksheets.Count
    With Worksheets(i)
        LR = .Cells(Rows.Count, 1).End(xlUp).Row
        For j = 9 To 56
            .Cells(LR + 2, j).Value = WorksheetFunction.Sum(.Range(.Cells(1, j), .Cells(LR, j)))
        Next j
    End With
Next i
End Sub

Wow! That works out really nice. I cannot thank you enough for making this so easy. Thanks!!