Solved: Sorting a non-active sheet

[JimS]

Is there a way to sort a non-active sheet?

Below is some code that adds a Totals row at the bottom.

I need to sort the rows before addding the Totals row.

Can this code be easily modified to do this?

Thanks...

Jim

[vba]
Sub AddTotals()
'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

Application.ScreenUpdating = False


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)))
Next iCol

sht.Cells(LastRow + 1, 1) = "Totals:"
End With

End If

Next sht

Application.ScreenUpdating = True

End Sub
[/vba]

[Bob Phillips]

Try this, but change the sort to suit

[vba]

Sub AddTotals()
'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

Application.ScreenUpdating = False


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(sht.Rows.Count, iCol).End(xlUp).Row
If iRow > LastRow Then LastRow = iRow
Next iCol

With sht

Range(.Cells(2, iCol), .Cells(LastRow, iCol)).Sort _
Key1:=.Cells(2, iCol), Order1:=xlAscending, _
Key2:=.Cells(2, iCol + 1), Order2:=xlAscending, _
Header:=xlYes

'Place column totals in row after current last row
For iCol = 2 To 10

.Cells(LastRow + 1, iCol) = Application.Sum(Range(.Cells(2, iCol), .Cells(LastRow, iCol)))
Next iCol

.Cells(LastRow + 1, 1) = "Totals:"
End With

End If

Next sht

Application.ScreenUpdating = True

End Sub
[/vba]

[JimS]

Got it to work perfectly, Thanks...

[Aussiebear]

You did or was it the response offered by Bob? Can you also please mark the thread as solved by using the Thread Tools options.