JimS
07-16-2009, 09:34 AM
What's wrong with this code (below)?
It is supposed to Sum Columns B-J (individually) and place the word "Totals:" in Column A of the last row - "only" on the sheets that begins with the name "Totals".
There are a few sheets that begin with the name Totals (ie: Totals1, Totals2, Totals3) in the workbook.
For some reason it will only run against the sheet that is selected at the time the macro runs, and it also runs through the code twice so that the sheet ends up with 2 "totals" rows at the bottom of the selected sheet. It never runs against the sheets thats names begin with "Totals".
Thanks...
Jim
Sub AddTotals3()
'This macro sums both the columns in columns B:J
'The column sums are placed after the last row.
'The last row is determined by the longest column of data.
Dim LastRow As Long
Dim iCol As Integer
Dim sht As Worksheet
LastRow = 0
For Each sht In ActiveWorkbook.Sheets
If Left(sht.Name, 6) = "Totals" Then
LastRow = 0
'Find last row in Columns B:J
For iCol = 2 To 10
iRow = Cells(65536, iCol).End(xlUp).Row
If iRow > LastRow Then LastRow = iRow
Next iCol
With Application.WorksheetFunction
'Place column totals in row after current last row
For iCol = 2 To 10
Cells(LastRow + 1, iCol) = .Sum(Range(Cells(2, iCol), Cells(LastRow, iCol)))
Next iCol
Cells(LastRow + 1, 1) = "Totals:"
End With
End If
Next sht
End Sub
It is supposed to Sum Columns B-J (individually) and place the word "Totals:" in Column A of the last row - "only" on the sheets that begins with the name "Totals".
There are a few sheets that begin with the name Totals (ie: Totals1, Totals2, Totals3) in the workbook.
For some reason it will only run against the sheet that is selected at the time the macro runs, and it also runs through the code twice so that the sheet ends up with 2 "totals" rows at the bottom of the selected sheet. It never runs against the sheets thats names begin with "Totals".
Thanks...
Jim
Sub AddTotals3()
'This macro sums both the columns in columns B:J
'The column sums are placed after the last row.
'The last row is determined by the longest column of data.
Dim LastRow As Long
Dim iCol As Integer
Dim sht As Worksheet
LastRow = 0
For Each sht In ActiveWorkbook.Sheets
If Left(sht.Name, 6) = "Totals" Then
LastRow = 0
'Find last row in Columns B:J
For iCol = 2 To 10
iRow = Cells(65536, iCol).End(xlUp).Row
If iRow > LastRow Then LastRow = iRow
Next iCol
With Application.WorksheetFunction
'Place column totals in row after current last row
For iCol = 2 To 10
Cells(LastRow + 1, iCol) = .Sum(Range(Cells(2, iCol), Cells(LastRow, iCol)))
Next iCol
Cells(LastRow + 1, 1) = "Totals:"
End With
End If
Next sht
End Sub