Solved: Macro not selecting correct sheet

[JimS]

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

[vba]
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
[/vba]

[p45cal]

try this (untested):[vba]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 = sht.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
sht.Cells(LastRow + 1, iCol) = .Sum(Range(sht.Cells(2, iCol), sht.Cells(LastRow, iCol)))
'the line above just might need to be the following commented -out line:
'sht.Cells(LastRow + 1, iCol) = .Sum(sht.Range(sht.Cells(2, iCol), sht.Cells(LastRow, iCol)))
Next iCol
sht.Cells(LastRow + 1, 1) = "Totals:"
End With
End If
Next sht
End Sub
[/vba]

[mdmackillop]

Hi Jim
Please use the green VBA button to format your code as shown.

[JimS]

Works now, Thanks for your expectise...

[JimS]

mdmackillop,

Ok - sorry...

Jim