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

chefdt

Board Regular
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

Excel Facts

Copy formula down without changing references
If you have =SUM(F2:F49) in F50; type Alt+' in F51 to copy =SUM(F2:F49) to F51, leaving the formula in edit mode. Change SUM to COUNT.

MickG

MrExcel MVP
Hi, Try this:-
Sum of each column inserted 2 rows after end of each column.
Code:
``````Dim wksh As Worksheet, rng As Range, oSum As Integer, oCol As Integer
For Each wksh In ActiveWorkbook.Worksheets

For oCol = 6 To 9
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

Last edited:

decklun

Board Regular
try this

Code:
``````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 = _
Next clm
Next ws``````

DonkeyOte

MrExcel MVP
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)

Replies
2
Views
279
Replies
4
Views
211
Replies
12
Views
169
Replies
16
Views
163
Replies
2
Views
55

1,191,719
Messages
5,988,295
Members
440,148
Latest member
sandy123

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back