Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Speed Up A Macro / Delete Worksheets
Any suggestions on speeding up this macro? Also, I'm working on a
macro to be able to delete these revision worksheets once I get the information I need. However, when I use the "For i To..." it is skipping every other worksheet (for obvious reasons). Any suggestions on that? Sub CopySortRev() ' Application.ScreenUpdating = False For i = 1 To 8 Worksheets(i).Copy After:=Worksheets(i + 8) Sheets(i + 9).Name = Sheets(i).Name & " Rev" Range("A2:E2").Select Range(Selection, Selection.End(xlDown)).Select Selection.Sort Key1:=Range("E2"), Order1:=xlAscending, Header:=xlGuess, _ OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _ DataOption1:=xlSortNormal Next i Application.ScreenUpdating = True End Sub Sub DeleteRev() For i = 10 To 17 Sheets(i).Select ActiveWindow.SelectedSheets.Delete Sheets(i).Delete Next i End Sub |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Speed Up A Macro / Delete Worksheets
Your code assumes there are only 9 worksheets. If that is a pre-condition
then should be ok. Otherwise you could use Worksheets.Count Also change the sort - no need to select data first. Specify if there is a heading or not using xlYes or xlNo changing xlGuess. Sub CopySortRev() Application.ScreenUpdating = False For i = 1 To 8 Worksheets(i).Copy After:=Worksheets(i + 8) Sheets(i + 9).Name = Sheets(i).Name & " Rev" Range("A2:E2").Sort Key1:=Range("E2"), Order1:=xlAscending, Header:=xlGuess, _ OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _ DataOption1:=xlSortNormal Next i Application.ScreenUpdating = True End Sub The reason sub DeleteRev it is not acting on all sheets is that the control values are increasing by 1 but you are removing the sheet at the same time. Change the code to start at 17 and count down to 10. I added the displayalerts to suppress the deletion warnings if you require this. Sub DeleteRev() Application.DisplayAlerts = False For i = 17 To 10 step -1 Sheets(i).Delete Next i Application.DisplayAlerts = True End Sub -- Cheers Nigel wrote in message oups.com... Any suggestions on speeding up this macro? Also, I'm working on a macro to be able to delete these revision worksheets once I get the information I need. However, when I use the "For i To..." it is skipping every other worksheet (for obvious reasons). Any suggestions on that? Sub CopySortRev() ' Application.ScreenUpdating = False For i = 1 To 8 Worksheets(i).Copy After:=Worksheets(i + 8) Sheets(i + 9).Name = Sheets(i).Name & " Rev" Range("A2:E2").Select Range(Selection, Selection.End(xlDown)).Select Selection.Sort Key1:=Range("E2"), Order1:=xlAscending, Header:=xlGuess, _ OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _ DataOption1:=xlSortNormal Next i Application.ScreenUpdating = True End Sub Sub DeleteRev() For i = 10 To 17 Sheets(i).Select ActiveWindow.SelectedSheets.Delete Sheets(i).Delete Next i End Sub |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Help, need to speed up this macro | Excel Discussion (Misc queries) | |||
Macro Speed | Excel Programming | |||
Speed-up a macro! | Excel Programming | |||
Speed up macro | Excel Discussion (Misc queries) | |||
Speed-up macro | Excel Programming |