ExcelBanter - 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