worksheet_SelectionChange for a specific column

[jmmac]

I have a worksheet that contains invoice numbers in column D. I'd like to copy the invoice number to another worksheet ("Details") when one is selected. I've added the "If IsNumeric" condition to make sure that only cells containing an invoice # can be copied over. The code is in the Sheet1 module, but seems to do nothing. Can anyone help point me in the right direction?
<code>

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
      If Target.Column = 4 Then
      On Error Resume Next
            Application.EnableEvents = False
            If IsNumeric(Target.Address) Then
                  ActiveSelection.Copy
                  Sheets("Detail").Range("A5").Select
                  ActiveSelection.Paste
            End If
            Application.EnableEvents = True
      End If
End Sub

</code>

 

[JoeMo]

I'd prefer both actions (copy target to Detail sheet and open Detail sheet) to occur with one click.

The blank cell being recognized as numeric brings up another problem - I only want cells with an invoice# to activate the Change event. The list of invoices varies daily, and is always D36:D?. I know how to determine the last row of data in a column, should I be defining a range rather than looking at Column 4?

This modification will ensure that selecting a blank cell does nothing. One click on any numeric cell in col 4 will do it - works for me with a single click.

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
      If Target.Column = 4 Then
             Set Target = Target.Cells(1, 1)
            If IsNumeric(Target.Value) And Not IsEmpty(Target.Value) Then
                  Sheets("Detail").Range("A5").Value = Target.Value
                Sheets("Detail").Activate
            End If
      End If
End Sub

 

[jmmac]

It seems to be working now. Takes about 5 seconds from the time I click until it changes sheets - I didn't realize it took a bit to run such a small macro, maybe I was just too impatient! Thanks for the help!

 

[JoeMo]

It seems to be working now. Takes about 5 seconds from the time I click until it changes sheets - I didn't realize it took a bit to run such a small macro, maybe I was just too impatient! Thanks for the help!

If the cell you are copying the ID to in sheet Detail is a precedent cell for formulas on that sheet or elsewhere, you may be seeing a delay while worksheets calculate. In that case, try this revision:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
      If Target.Column = 4 Then
             Set Target = Target.Cells(1, 1)
            If IsNumeric(Target.Value) And Not IsEmpty(Target.Value) Then
                Application.Calculation = xlCalculationManual
                  Sheets("Detail").Range("A5").Value = Target.Value
                Sheets("Detail").Activate
                Application.Calculation = xlCalculationAutomatic
            End If
      End If
End Sub

 

[jmmac]

That did it, you're the best!