looping through worksheets, sorting and subtotals

[Przemek]

Hi,

I have a workbook with various worksheets, which I split depending on
what is in column C, than next split (based on value in another
column), msg-id: (e-mail address removed)

Everything it's fine, saves me a lot of time. But at the end I need
sort these worksheets (by column E) and subtotal them. Here is my code:

Sub SubTotals(WorkbookName As String)
Dim rng As Range
Dim wks As Worksheet
For Each wks In Workbooks(WorkbookName).Worksheets
On Error Resume Next

lastRow = Cells.SpecialCells(xlLastCell).Row
lastCol = Cells.SpecialCells(xlLastCell).Column
Set rg = Range("A1", Cells(lastRow, lastCol))
rg.Select
rg.Sort Key1:=Range("E2"), Order1:=xlAscending, Header:= _
xlYes, OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom,
_
DataOption1:=xlSortNormal
rg.Select
Selection.Subtotal GroupBy:=5, Function:=xlSum, TotalList:=Array(6,
12), Replace:=True, PageBreaks:=False, SummaryBelowData:=True
Next wks
End Sub

The problems is (I suppose) rg object. Macro is selecting right range
on first sheet, but on the others variables lastRow and lastCol are not
changing at all. Still got values from the first one How can I
select all rows with data?

And second thing, when my macro should subtotal, it's sometimes doing
this and sometimes leaving sheet as it was.

Przemek

 

[William Horton]

I think the lastrow and lastcol variables are not changing because when you
are looping through the worksheets you are not activating them. Try entering
wks.activate
on the line under your For Each wks in ........ statement.

Hope this helps.

Bill Horton

 

[Dave Peterson]

You can do all this stuff without selecting ranges (and ranges can only be
selected on the activesheet and sheets can only be selected in the
activeworkbook).

Option Explicit

Sub mySubTotals(WorkbookName As String)

Dim wks As Worksheet
Dim LastRow As Long
Dim LastCol As Long
Dim rg As Range
For Each wks In Workbooks(WorkbookName).Worksheets
With wks
LastRow = .Cells.SpecialCells(xlLastCell).Row
LastCol = .Cells.SpecialCells(xlLastCell).Column
Set rg = .Range("A1", .Cells(LastRow, LastCol))
rg.Sort Key1:=.Range("E2"), Order1:=xlAscending, _
Header:=xlYes, OrderCustom:=1, MatchCase:=False, _
Orientation:=xlTopToBottom, DataOption1:=xlSortNormal
rg.Subtotal GroupBy:=5, Function:=xlSum, _
TotalList:=Array(6, 12), Replace:=True, PageBreaks:=False, _
SummaryBelowData:=True
End With
Next wks
End Sub

I also changed the name of the procedure--subtotals is a property in VBA.

 

[Dave Peterson]

And since the workbook might not be active, the OP will have to add:

workbooks(workbookname).activate
wks.activate