Won't sort on deactivate

[ron.bo]

I have code on a worksheet to sort data before going to another worksheet. However, I keep getting an error and the data range is highlighted. the code is;

Private Sub Worksheet_Deactivate()
Columns("B:F").Select
Selection.Sort Key1:=Range("B2"), Order1:=xlAscending, Key2:=Range("C2") _
, Order2:=xlAscending, Header:=xlGuess, OrderCustom:=1, MatchCase:= _
False, Orientation:=xlTopToBottom, DataOption1:=xlSortNormal, DataOption2 _
:=xlSortNormal
End Sub

One note is that column A is hidden with formulas that relate to columns A & B. However, the code works fine in the worksheet.

Any help would be appreciated.

Ron

 

[dk]

I suspect that it doesn't like that you're trying to select something when the sheet is being deactivated. Try this instead - it's better to not select anything if you can get away with it.

Private Sub Worksheet_Deactivate()
Me.Range("B:F").Sort Key1:=Range("B2"), Order1:=xlAscending, Key2:=Range("C2") _
, Order2:=xlAscending, Header:=xlGuess, OrderCustom:=1, MatchCase:= _
False, Orientation:=xlTopToBottom, DataOption1:=xlSortNormal, DataOption2 _
:=xlSortNormal
End Sub

HTH
DK

 

[ron.bo]

Thank you for your help and expertise... it works perfect!