I like using the Me keyword, too. But I'd add it to the the key, too.

Private Sub Worksheet_Deactivate()
Me.Range("A1:A10").Sort Key1:=me.Range("A1"), Order1:=xlAscending
End Sub

Rowan wrote:

Private Sub Worksheet_Deactivate()
Me.Range("A1:A10").Sort Key1:=Range("A1"), Order1:=xlAscending
End Sub

Regards
Rowan

"Jay Northrop" wrote:

Hello!

I'm having trouble with Deactivate Sheet. My workbook has two sheets, Sheet1
and Sheet2. In Sheet1, I have this macro:

Private Sub Worksheet_Deactivate()
Worksheets("Sheet1").Activate
Range("A1:A10").Select
Selection.Sort Key1:=Range("A1"), Order1:=xlAscending
End Sub

However, when I click on Sheet2, Sheet1 won't "let go." I suppose it's
because of the third line [Range("A1:A10").Select]. but, without that line,
I get an error message. How do I get the macro to "let go" of sheet 1 and go
to sheet 2 when I click on Sheet2?

Also, I tested another similar Worksheet_Deactivate macro (not using a sort
routine) which worked okay, but it left a selection "shadow" on Sheet2.

Your help is appreciated.

Thanks,

Jay




--

Dave Peterson