Macro to sum columns in all sheets in a workbook
 

I need to write a macro to sum the same columns (ie.. G:O) in every worksheet
within a workbook. I need to add it to a macro that I already have which
splits sheet 1 into other sheets using the information in column A. I have
that macro working as well as using an auto fit macro for all sheets, but now
I need to total the columns in each sheet that was created. I have a macro
that works when it is one sheet but I don't know how to do it for all
sheets. Also, the columns in each sheet will end in different rows.

--
Thank you, Jodie

Macro to sum columns in all sheets in a workbook
 

Something like this. You have to skip the original shet that you split
and I included row 1 which you also may want to change to row 2. I'm
using column A to determine the last row and putting the total and the
next row after the last row.

Sub test()

firstCol = ActiveSheet.Columns("G").Column
LastCol = ActiveSheet.Columns("O").Column


For Each Sht In Sheets
With Sht
'find last row using column A
LastRow = .Range("A" & Rows.Count).End(xlUp).Row
SumRow = LastRow + 1
'Add formula to worksheet to add first column
Set SumRange = .Range(.Cells(1, firstCol), .Cells(LastRow,
firstCol))
.Cells(SumRow, firstCol).Formula = _
"=Sum(" & SumRange.Address(ColumnAbsolute:=False) & ")"

'copy the formula across all the columns
.Cells(SumRow, firstCol).Copy _
Destination:=.Range(.Cells(SumRow, firstCol), .Cells(SumRow,
LastCol))
'Put Total in column a
.Range("A" & SumRow) = "Total"
End With
Next Sht

End Sub


--
joel
------------------------------------------------------------------------
joel's Profile: http://www.thecodecage.com/forumz/member.php?userid=229
View this thread: http://www.thecodecage.com/forumz/sh...d.php?t=148941

Macro to sum columns in all sheets in a workbook
 

Thank you Joel. I will give it a try and let you know how it goes.
--
Thank you, Jodie


"joel" wrote:


Something like this. You have to skip the original shet that you split
and I included row 1 which you also may want to change to row 2. I'm
using column A to determine the last row and putting the total and the
next row after the last row.

Sub test()

firstCol = ActiveSheet.Columns("G").Column
LastCol = ActiveSheet.Columns("O").Column


For Each Sht In Sheets
With Sht
'find last row using column A
LastRow = .Range("A" & Rows.Count).End(xlUp).Row
SumRow = LastRow + 1
'Add formula to worksheet to add first column
Set SumRange = .Range(.Cells(1, firstCol), .Cells(LastRow,
firstCol))
.Cells(SumRow, firstCol).Formula = _
"=Sum(" & SumRange.Address(ColumnAbsolute:=False) & ")"

'copy the formula across all the columns
.Cells(SumRow, firstCol).Copy _
Destination:=.Range(.Cells(SumRow, firstCol), .Cells(SumRow,
LastCol))
'Put Total in column a
.Range("A" & SumRow) = "Total"
End With
Next Sht

End Sub


--
joel
------------------------------------------------------------------------
joel's Profile: http://www.thecodecage.com/forumz/member.php?userid=229
View this thread: http://www.thecodecage.com/forumz/sh...d.php?t=148941

.

Macro to sum columns in all sheets in a workbook
 

Hi Joel, I tried it and I am getting an error.

Compile error
Syntax error

It occurs at Set SumRange = .Range(.Cells(1, firstCol), .Cells(LastRow,
firstCol))

I should tell you that I am adding this to a module that I already have that
creates the sheets. What I have that already creates the sheets may creat a
sheet with only one row which is the header. There are 2 sheets created with
no records except the header. They are named NULL and PFSPLANID. I tried
deleting those sheets and rerunning, but I get the same error. Can you
please help me figure out what I am doing wrong?
--
Thank you, Jodie


"Jodie" wrote:

Thank you Joel. I will give it a try and let you know how it goes.
--
Thank you, Jodie


"joel" wrote:


Something like this. You have to skip the original shet that you split
and I included row 1 which you also may want to change to row 2. I'm
using column A to determine the last row and putting the total and the
next row after the last row.

Sub test()

firstCol = ActiveSheet.Columns("G").Column
LastCol = ActiveSheet.Columns("O").Column


For Each Sht In Sheets
With Sht
'find last row using column A
LastRow = .Range("A" & Rows.Count).End(xlUp).Row
SumRow = LastRow + 1
'Add formula to worksheet to add first column
Set SumRange = .Range(.Cells(1, firstCol), .Cells(LastRow,
firstCol))
.Cells(SumRow, firstCol).Formula = _
"=Sum(" & SumRange.Address(ColumnAbsolute:=False) & ")"

'copy the formula across all the columns
.Cells(SumRow, firstCol).Copy _
Destination:=.Range(.Cells(SumRow, firstCol), .Cells(SumRow,
LastCol))
'Put Total in column a
.Range("A" & SumRow) = "Total"
End With
Next Sht

End Sub


--
joel
------------------------------------------------------------------------
joel's Profile: http://www.thecodecage.com/forumz/member.php?userid=229
View this thread: http://www.thecodecage.com/forumz/sh...d.php?t=148941

.

Macro to sum columns in all sheets in a workbook
 

Hi Joel, I figured out the problem. Thank you very much for your help.
--
Thank you, Jodie


"Jodie" wrote:

Hi Joel, I tried it and I am getting an error.

Compile error
Syntax error

It occurs at Set SumRange = .Range(.Cells(1, firstCol), .Cells(LastRow,
firstCol))

I should tell you that I am adding this to a module that I already have that
creates the sheets. What I have that already creates the sheets may creat a
sheet with only one row which is the header. There are 2 sheets created with
no records except the header. They are named NULL and PFSPLANID. I tried
deleting those sheets and rerunning, but I get the same error. Can you
please help me figure out what I am doing wrong?
--
Thank you, Jodie


"Jodie" wrote:

Thank you Joel. I will give it a try and let you know how it goes.
--
Thank you, Jodie


"joel" wrote:


Something like this. You have to skip the original shet that you split
and I included row 1 which you also may want to change to row 2. I'm
using column A to determine the last row and putting the total and the
next row after the last row.

Sub test()

firstCol = ActiveSheet.Columns("G").Column
LastCol = ActiveSheet.Columns("O").Column


For Each Sht In Sheets
With Sht
'find last row using column A
LastRow = .Range("A" & Rows.Count).End(xlUp).Row
SumRow = LastRow + 1
'Add formula to worksheet to add first column
Set SumRange = .Range(.Cells(1, firstCol), .Cells(LastRow,
firstCol))
.Cells(SumRow, firstCol).Formula = _
"=Sum(" & SumRange.Address(ColumnAbsolute:=False) & ")"

'copy the formula across all the columns
.Cells(SumRow, firstCol).Copy _
Destination:=.Range(.Cells(SumRow, firstCol), .Cells(SumRow,
LastCol))
'Put Total in column a
.Range("A" & SumRow) = "Total"
End With
Next Sht

End Sub


--
joel
------------------------------------------------------------------------
joel's Profile: http://www.thecodecage.com/forumz/member.php?userid=229
View this thread: http://www.thecodecage.com/forumz/sh...d.php?t=148941

.