On Deactivate Sheet Event 1

[JamieArvato]

I've setup code that runs on Worksheet_Deactivate, what it does is sort the data BUT.....because it is sorting on a range on the sheet that is being deactivated it goes back to that sheet and wont move to the new selected sheet and just loops like that.

Is there a way of knowing which sheet has been clicked on, storing this value then saying data sort on current sheet and go to new sheet after........?

Thanks,

Jamie.

 

[SkipVought]

Please post your code.

Skip,
[sub]
[red]Be advised:[/red] When transmitting sheet music...
If it ain't baroque, don't fax it! [/sub]

 

[JamieArvato]

Code:

Private Sub Worksheet_Deactivate()

' On Sheet Activate Sort on Name For Lookup Purposes

Application.Goto Reference:="ShiftPlan"
Selection.Sort Key1:=Range("A11"), Order1:=xlAscending, Header:=xlGuess, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
DataOption1:=xlSortNormal

End Sub

The "ShiftPlan" range is on the sheet that this code is on.

 

[SkipVought]

try thsi

Private Sub Worksheet_Deactivate()

' On Sheet Activate Sort on Name For Lookup Purposes
   
       Range("ShiftPlan").Sort Key1:=Range("A11"), Order1:=xlAscending, Header:=xlGuess, _
           OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
           DataOption1:=xlSortNormal

End Sub

Skip,
[sub]
[red]Be advised:[/red] When transmitting sheet music...
If it ain't baroque, don't fax it! [/sub]

 

[SkipVought]

Actually, you may need another regerence for the KEY

Private Sub Worksheet_Deactivate()

' On Sheet Activate Sort on Name For Lookup Purposes
   
       With Range("ShiftPlan")
         .Sort Key1:=.Parent.Range("A11"), Order1:=xlAscending, Header:=xlGuess, _
           OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
           DataOption1:=xlSortNormal

End Sub

Skip,
[sub]
[red]Be advised:[/red] When transmitting sheet music...
If it ain't baroque, don't fax it! [/sub]

 

[SkipVought]

oops...

forgot End With

Private Sub Worksheet_Deactivate()

' On Sheet Activate Sort on Name For Lookup Purposes
   
       With Range("ShiftPlan")
         .Sort Key1:=.Parent.Range("A11"), Order1:=xlAscending, Header:=xlGuess, _
           OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
           DataOption1:=xlSortNormal
      End With
End Sub

Skip,
[sub]
[red]Be advised:[/red] When transmitting sheet music...
If it ain't baroque, don't fax it! [/sub]