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>

 

[BiocideJ]

I haven't really studied the entire code, but I noticed one thing.
I think the problem is that you are testing if the Address IsNumeric however Target.Address will always return text (e.g. "$A$1", "$Z$22", etc) I believe that should be replaced with Target.Value

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

Also, the change in Blue is an easier way to copy/paste

 

[jmmac]

That makes sense. I changed the code, but still nothing happens regardless of where I click.

 

[JoeMo]

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
      Set Target = Target.Cells(1, 1)
            Application.EnableEvents = False
            If IsNumeric(Target.Value) Then
                  Sheets("Detail").Range("A5").Value = Target.Value
            End If
            Application.EnableEvents = True
      End If
End Sub

</code>

The red bits are not legal syntax. Try this:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
      If Target.Column = 4 Then Set Target = Target.Cells(1, 1)
            Application.EnableEvents = False
            If IsNumeric(Target.Value) Then
                  Sheets("Detail").Range("A5").Value = Target.Value
            End If
            Application.EnableEvents = True
      End If
End Sub

 

[JoeMo]

The red bits are not legal syntax. Try this:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
      If Target.Column = 4 Then Set Target = Target.Cells(1, 1)
            Application.EnableEvents = False
            If IsNumeric(Target.Value) Then
                  Sheets("Detail").Range("A5").Value = Target.Value
            End If
            Application.EnableEvents = True
      End If
End Sub

Change that to this:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
      If Target.Column = 4 Then
             Set Target = Target.Cells(1, 1)
            Application.EnableEvents = False
            If IsNumeric(Target.Value) Then
                  Sheets("Detail").Range("A5").Value = Target.Value
            End If
            Application.EnableEvents = True
      End If
End Sub

 

[jmmac]

Works perfect, thanks Joe!

I've added: Sheets("Detail").Activate
below the line: Sheets("Detail").Range("A5").Value = Target.Value
hoping that when an invoice is selected, the second worksheet will open, but it does not. Any suggestions on this?

 

[JoeMo]

Works perfect, thanks Joe!

I've added: Sheets("Detail").Activate
below the line: Sheets("Detail").Range("A5").Value = Target.Value
hoping that when an invoice is selected, the second worksheet will open, but it does not. Any suggestions on this?

Try this:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
      If Target.Column = 4 Then
             Set Target = Target.Cells(1, 1)
            Application.EnableEvents = False
            If IsNumeric(Target.Value) Then
                  Sheets("Detail").Range("A5").Value = Target.Value
                Application.EnableEvents = True
                Sheets("Detail").Activate
            End If
      End If
End Sub

 

[jmmac]

That works, but only when I click a second time. I tried removing the Application.EnableEvents lines and had the same result - one click on an invoice # updates sheet2, a second click (anywhere on sheet1) opens sheet2.

 

[JoeMo]

That works, but only when I click a second time. I tried removing the Application.EnableEvents lines and had the same result - one click on an invoice # updates sheet2, a second click (anywhere on sheet1) opens sheet2.

One click on any cell in column D should activate a sheet named "Detail" if either the cell has a number in it or the cell is blank (VBA sees a blank cell as numeric with the value 0). And, yes you can remove both Application.EnableEvents lines they aren't necessary. Do you need a 2nd click when selecting a cell with a number or a blank in col D?

 

[jmmac]

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?