I have written this for a combo box routine. Now the spreadsheet takes ages to open. I assume that this code is to much.
Can anyone suggest how i could trim it down or have other suggestions why my spreadsheet is so slow opening. here is the code i am using.
Private Sub ComboBox1_Change()
Application.ScreenUpdating = False
Worksheets("Main").Range("f1").Value = ComboBox1.ListIndex + 1
Range("a16:d40").Select
Selection.Clear
Sheets("Staff").Select
Sheets("Staff").Activate
Application.CutCopyMode = False
Selection.AutoFilter
Selection.AutoFilter Field:=1, Criteria1:=ComboBox1.Value
Worksheets("Staff").Range("a2:e5000").Copy
Worksheets("Main").Range("a16").PasteSpecial (xlPasteValues)
Selection.AutoFilter
Sheets("Main").Activate
Worksheets("Main").Range("a16").Activate
Range("a16").Select
Range("a16").Activate
'Range("a16:d40").Select
Selection.sort Key1:=Range("B16"), Order1:=xlAscending, Key2:=Range("C12" _
), Order2:=xlAscending, Header:=xlGuess, OrderCustom:=1, MatchCase:= _
False, Orientation:=xlTopToBottom
Sheets("Main").Range("b3").Activate
Application.ScreenUpdating = True
End Sub
Tks for any assistance
Can anyone suggest how i could trim it down or have other suggestions why my spreadsheet is so slow opening. here is the code i am using.
Private Sub ComboBox1_Change()
Application.ScreenUpdating = False
Worksheets("Main").Range("f1").Value = ComboBox1.ListIndex + 1
Range("a16:d40").Select
Selection.Clear
Sheets("Staff").Select
Sheets("Staff").Activate
Application.CutCopyMode = False
Selection.AutoFilter
Selection.AutoFilter Field:=1, Criteria1:=ComboBox1.Value
Worksheets("Staff").Range("a2:e5000").Copy
Worksheets("Main").Range("a16").PasteSpecial (xlPasteValues)
Selection.AutoFilter
Sheets("Main").Activate
Worksheets("Main").Range("a16").Activate
Range("a16").Select
Range("a16").Activate
'Range("a16:d40").Select
Selection.sort Key1:=Range("B16"), Order1:=xlAscending, Key2:=Range("C12" _
), Order2:=xlAscending, Header:=xlGuess, OrderCustom:=1, MatchCase:= _
False, Orientation:=xlTopToBottom
Sheets("Main").Range("b3").Activate
Application.ScreenUpdating = True
End Sub
Tks for any assistance