On Jul 19, 10:36 am, Joel <J...@discussions.microsoft.com> 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
>
> "transfer...@gmail.com" 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
thxs a lot!!!
Similar Threads
