Macro for SUM of colums....many worksheets!

[chefdt]

I have a workbook with 90+ sheets. Each sheet's format is the same, with different data. I need a macro that will SUM four different columns from EACH sheet. The number of ROWS in each sheet varies....

The columns are F,G,H,I, and the data starts in ROW 2.

Can anyone help?

Dale

 

[MickG]

Hi, Try this:-
Sum of each column inserted 2 rows after end of each column.

Dim wksh As Worksheet, rng As Range, oSum As Integer, oCol As Integer
Dim oAd, oLet
For Each wksh In ActiveWorkbook.Worksheets
   
   For oCol = 6 To 9
    oAd = Cells(2, oCol).Address(0, 0)
        oLet = Split(Cells(2, oCol).Address, "$")(1)
            Set rng = Sheets(wksh.Name).Range(oAd, Sheets(wksh.Name) _
                .Range(oLet & Rows.Count).End(xlUp))
                     oSum = WorksheetFunction.Sum(rng)
            Sheets(wksh.Name).Cells(rng.Count + 3, oCol) = oSum
    Next oCol
Next wksh

MsgBox "Sums Complete"

Regards Mick

 

[decklun]

try this

Dim wb As Workbook
Dim ws As Worksheet
Dim LstShtRow As Long
Dim clm As Integer

Set wb = ThisWorkbook

For Each ws In wb.Worksheets
    'find the last row assuming each column has the same amount
    'of rows, and is looking in column A for the last row
    LstShtRow = ws.Cells(Rows.Count, 1).End(xlUp).Row
    'sum columns F,G,H,I or 6,7,8,9
    For clm = 6 To 9
        ws.Cells(LstShtRow + 1, clm).Formula = _
            "=SUM(" & ws.Cells(2, clm).Address & ":" & ws.Cells(LstShtRow, clm).Address & ")"
    Next clm
Next ws

 

[DonkeyOte]

if the sheets are positioned in sequence insert a sheet called START before first sheet and another called END after the last of the 90 sheets -- then simply use

=SUM(START:END!F:I)