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" wrote in message
...
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