Change event copy & paste

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

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 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

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



"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 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

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

"Mr. Dan" wrote:

> 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

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





"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 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

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

"Mr. Dan" wrote:

> 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
>
>
>
>
>
> "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 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

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