Code cleanup assistance

[Cossie]

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

 

[zacemmel]

Avoid using "activate" and "select." It causes a lot of unneeded processor usage. You can reference cells without selecting them first. For example:

range("a1").select
selection.value = 1

could be:

range("a1").value = 1

Hope this helps.

Questions?

http://www.excelquestions.com

 

[Cossie]

Thanks for the response will give it a go and let u know

 

[Cossie]

are you able to be a little more specific pls.

How would this code change?

Sheets("Staff").Select
Sheets("Staff").Activate

 

[zacemmel]

Set staff=sheets("Staff")


You can now reference that sheet by doing this:

staff.range("a1").value = "myvalue"

Questions?

http://www.excelquestions.com

 

[Cossie]

sorry i dont follow

 

[Cossie]

can anyone help me with this problem