Extra added to a script

In the A Column I would like the date to be entered as well?

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Count > 1 Then Exit Sub
If Target.Address = "$E$162" Then
With Sheets("ChequesOut")
If .[C1] = "" Then
Union([B162], [E162]).Copy .[C1]
Else
Union([B162], [E162]).Copy .Range("C" & Rows.Count). _
End(xlUp)(2)
End If
[B162].Select
End With
End If
End Sub


Thanks in advance.........Bob Vance

Using [C1] is slower than using Range("C1") and for me, I find it more difficult
to read.

But something like this might work ok:

Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)
Dim NextRow As Long
If Target.Address = "$E$162" Then

With Sheets("ChequesOut")

If .Range("C1").Value = "" Then
NextRow = 1
Else
NextRow = .Range("C" & Rows.Count).End(xlUp).Offset(1, 0).Row
End If

Me.Range("b162,E162").Copy _
Destination:=.Cells(NextRow, "C")

With .Cells(NextRow, "A")
.Value = Now 'date ???
.NumberFormat = "mm/dd/yyyy hh:mm:ss" 'mm/dd/yyyy ????
End With

End With

Me.Range("B162").Select
End If
End Sub

Bob wrote:
>
> In the A Column I would like the date to be entered as well?
>
> Private Sub Worksheet_Change(ByVal Target As Range)
> If Target.Count > 1 Then Exit Sub
> If Target.Address = "$E$162" Then
> With Sheets("ChequesOut")
> If .[C1] = "" Then
> Union([B162], [E162]).Copy .[C1]
> Else
> Union([B162], [E162]).Copy .Range("C" & Rows.Count). _
> End(xlUp)(2)
> End If
> [B162].Select
> End With
> End If
> End Sub
>
> Thanks in advance.........Bob Vance

--

Dave Peterson

Brilliant Thank You Dave

"Dave Peterson" <[email protected]> wrote in message
news:[email protected]...
> Using [C1] is slower than using Range("C1") and for me, I find it more
> difficult
> to read.
>
> But something like this might work ok:
>
> Option Explicit
> Private Sub Worksheet_Change(ByVal Target As Range)
> Dim NextRow As Long
> If Target.Address = "$E$162" Then
>
> With Sheets("ChequesOut")
>
> If .Range("C1").Value = "" Then
> NextRow = 1
> Else
> NextRow = .Range("C" & Rows.Count).End(xlUp).Offset(1,
> 0).Row
> End If
>
> Me.Range("b162,E162").Copy _
> Destination:=.Cells(NextRow, "C")
>
> With .Cells(NextRow, "A")
> .Value = Now 'date ???
> .NumberFormat = "mm/dd/yyyy hh:mm:ss" 'mm/dd/yyyy ????
> End With
>
> End With
>
> Me.Range("B162").Select
> End If
> End Sub
>
> Bob wrote:
>>
>> In the A Column I would like the date to be entered as well?
>>
>> Private Sub Worksheet_Change(ByVal Target As Range)
>> If Target.Count > 1 Then Exit Sub
>> If Target.Address = "$E$162" Then
>> With Sheets("ChequesOut")
>> If .[C1] = "" Then
>> Union([B162], [E162]).Copy .[C1]
>> Else
>> Union([B162], [E162]).Copy .Range("C" & Rows.Count). _
>> End(xlUp)(2)
>> End If
>> [B162].Select
>> End With
>> End If
>> End Sub
>>
>> Thanks in advance.........Bob Vance
>
> --
>
> Dave Peterson