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
|