On Jul 19, 10:36 am, Joel wrote:
Sub Statement()

For Each ws In Worksheets
ws.Range("A1").CurrentRegion.Sort _
Key1:=Range("F2"), Order1:=xlAscending, _
Key2:=Range("D2"), _
Order2:=xlAscending, _
header:=xlGuess, _
OrderCustom:=1, _
MatchCase:=False, _
Orientation:=xlTopToBottom, _
DataOption1:=xlSortNormal, _
DataOption2:=xlSortNormal

ws.Range("A1").CurrentRegion.Subtotal _
GroupBy:=6, Function:=xlSum, _
TotalList:=Array(7), _
Replace:=True, _
PageBreaks:=False, _
SummaryBelowData:=True

ws.Range("A1").CurrentRegion.Replace _
What:="SUBTOTAL(9,", _
Replacement:="sum(", _
LookAt:=xlPart, _
SearchOrder:=xlByRows, _
MatchCase:=False, _
SearchFormat:=False, _
ReplaceFormat:=False

ws.Range("A1").CurrentRegion.AutoFormat _
Format:=xlRangeAutoFormatSimple, _
Number:=True, _
Font:=False, _
Alignment:=False, _
Border:=True, _
Pattern:=True, _
Width:=False

ws.Columns("G:G").Select
Selection.Replace _
What:="SUBTOTAL(9,", _
Replacement:="SUM(", _
LookAt:=xlPart, _
SearchOrder:=xlByRows, _
MatchCase:=False, _
SearchFormat:=False, _
ReplaceFormat:=False

Next ws
End Sub

" wrote:
Can anybody modify my macro below works on all worksheets in my
workbook (loop) - instead of applying the macro to individual sheets

Sub Statement()

Range("A1").CurrentRegion.Sort Key1:=Range("F2"), Order1:=xlAscending,
Key2:=Range("D2") _
, Order2:=xlAscending, header:=xlGuess, OrderCustom:=1,
MatchCase:= _
False, Orientation:=xlTopToBottom, DataOption1:=xlSortNormal,
DataOption2 _
:=xlSortNormal

Range("A1").CurrentRegion.Subtotal GroupBy:=6, Function:=xlSum,
TotalList:=Array(7), _
Replace:=True, PageBreaks:=False, SummaryBelowData:=True

Range("A1").CurrentRegion.Replace What:="SUBTOTAL(9,",
Replacement:="sum(", LookAt:= _
xlPart, SearchOrder:=xlByRows, MatchCase:=False,
SearchFormat:=False, _
ReplaceFormat:=False

Range("A1").CurrentRegion.AutoFormat Format:=xlRangeAutoFormatSimple,
Number:=True, Font _
:=False, Alignment:=False, Border:=True, Pattern:=True,
Width:=False

Columns("G:G").Select
Selection.Replace What:="SUBTOTAL(9,", Replacement:="SUM(",
LookAt:= _
xlPart, SearchOrder:=xlByRows, MatchCase:=False,
SearchFormat:=False, _
ReplaceFormat:=False

End Sub

Thxs

Too good to be true - It does not work - could you pls retry or
someone else help - thxs