Home Menu

Menu


ExcelBanter » Excel Newsgroups » Excel Programming > apply Macro to all sheets in workbook - loop
Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 31
Default apply Macro to all sheets in workbook - loop
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

Reply With QuoteReply With Quote
  #2   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 9,101
Default apply Macro to all sheets in workbook - loop
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


Reply With QuoteReply With Quote
  #3   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 31
Default apply Macro to all sheets in workbook - loop
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

thxs a lot!!!

Reply With QuoteReply With Quote
  #4   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 31
Default apply Macro to all sheets in workbook - loop
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


Reply With QuoteReply With Quote
Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes
Linear Mode Linear Mode

Posting Rules
Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On

Similar Threads
Thread Thread Starter Forum Replies Last Post
Apply Conditional Format to all sheets in same workbook Der Musensohn Excel Discussion (Misc queries) 4 February 26th 10 04:24 PM
Apply Macro to all worksheets in a workbook except one Carrie_Loos via OfficeKB.com Excel Discussion (Misc queries) 5 January 15th 09 10:03 PM
how to apply the same header or footer to all sheets in workbook Luenda Burke Excel Worksheet Functions 1 November 1st 06 05:45 PM
apply a macro to all sheets except for a certain sheet minrufeng Excel Discussion (Misc queries) 4 February 22nd 06 02:53 PM
Loop through all sheets in workbook jennie Excel Programming 7 October 26th 05 11:54 AM


All times are GMT +1. The time now is 05:52 AM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"

 

Contact Us ExcelBanter forum home Privacy Statement