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 |
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 |
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!!! |
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 |
All times are GMT +1. The time now is 02:49 PM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com