RE: Change event copy & paste

Tech-Archive recommends: Speed Up your PC by fixing your registry

Whoops, looks like I jumped the gun here.

The first work*** 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 work***?


Thanks in advance!
Dan





"Toppers" wrote:

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

"Mr. Dan" wrote:

Hello,

I have a financial work*** 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
.

Quantcast