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

workbooks(workbookname).activate
wks.activate

William Horton wrote:

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

"Przemek" wrote:

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:

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



--

Dave Peterson