Change event copy & paste

[Guest]

Hello,

I have a financial worksheet where column E lists "credits" and column F
lists "debits" and column G lists the credits minus the debits and then adds
this result to the running balance.

I wanted to add a change event so that whenever a value is typed into
columns E or F, the value in column G would automatically copy down from the
cell directly above it.

I've tried something similar to the following code, but I know I'm way off.

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address = "E:E" Then
If Target.Address > "0" Then
Range(Target).Offset(-1, 2).Copy Range(Target).Offset(0, 2)
End If
End If

If Target.Address = "F:F" Then
If Target.Address > "0" Then
Range(Target.Address).Offset(-1, 1).Copy
Range(Target.Address).Offset(0, 1)
End If
End If
End Sub

Any help would be great! Thank you!!!
Dan

 

[Guest]

Dan,
Try this:

Private Sub Worksheet_Change(ByVal Target As Range)
On Error GoTo wsexit
Application.EnableEvents = False
If Not Intersect(Target, Range("E:E")) Is Nothing Then
Range(Target.Address).Offset(-1, 2).Copy
Range(Target.Address).Offset(0, 2)
Else
If Not Intersect(Target, Range("F:F")) Is Nothing Then
Range(Target.Address).Offset(-1, 1).Copy
Range(Target.Address).Offset(0, 1)
End If
End If
wsexit:
Application.EnableEvents = True
End Sub

 

[Guest]

Worked like a charm! Many thanks for the quick response.

 

[Guest]

If Target.Row = 5 Then
If Target.Column > 0 Then

 

[Guest]

Whoops, looks like I jumped the gun here.

The first worksheet I used the code in worked fine. However, I used the
same code in two other worksheets within the same workbook and neither of
them work.

Do I have to use different variables for each worksheet?


Thanks in advance!
Dan

 

[Guest]

Dan,
No ... same code will do placed in the worksheet code. Try
running this in each sheet (click on tab and "View code"):

Sub AA()
Application.EnableEvents = True
End Sub

to ensure the event will trigger

HTH

 

[Guest]

Toppers,

Thank you for the response. I'm embarrased to say, but the other two sheets
that weren't working did not have the same exact columns as the original
sheet and this is why the event was not triggering properly.

Thanks again,
Dan