Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 . |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 . |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 . |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
In need to link Columns between sheets of a workbook? | Excel Discussion (Misc queries) | |||
Resize columns for all sheets in a workbook | Excel Programming | |||
macro to compile columns on multiple sheets | Excel Discussion (Misc queries) | |||
Macro for filter on protected workbook that works for all sheets, no matter what sheets are named? | Excel Programming | |||
comparing 2 similar columns on seperate work sheets in 1 workbook | Excel Discussion (Misc queries) |