Need to run macro when leaving worksheet

[cdiwindows]

I have a workbook that the end user will paste the values from a workbook that is generated from another application. Columns D:L are numeric but when pasted into my spreadsheet, they are coming in as text. I have the code to convert all these values to numeric values that I would like to run when the user clicks another sheet.

I tried putting the code in the Private Sub Worksheet_Deactivate, but after the code runs it stays on the sheet I am trying to leave. How can I tell which sheet was clicked on and move to that sheet after the code runs.

Here is the code I have:

Private Sub Worksheet_Deactivate()

Sheets("CAR_W1").Select
    Range("D9:L207").Select
    For Each xcell In Selection
        xcell.Value = xcell.Value
    Next xcell
    
End Sub

Also, is there a better way to convert all the text numbers to actual numbers?

Thank You

 

[SkipVought]

hi,

...but after the code runs it stays on the sheet I am trying to leave.

Avoid using the Select method...

Private Sub Worksheet_Deactivate()

  With Sheets("CAR_W1")
    
    For Each xcell In .Range("D9:L207")
        xcell.Value = xcell.Value
    Next xcell

  end with  
End Sub

or this, requireres no loop...

Private Sub Worksheet_Deactivate()

  With Sheets("CAR_W1")
    with .cell(65536,"IV")
      .value = 1
      .copy
    end with

    .Range("D9:L207").pastespecial xlmultiply

    .cell(65536,"IV").clear
  end with  
End Sub

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[cdiwindows]

Thanks Skip!

Your first example worked great. The second I received a run-time error 438 - Object doesn't support this property or method.