JimS
07-16-2009, 12:09 PM
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
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
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
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