Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Change event copy & paste
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Change event copy & paste
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Change event copy & paste
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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Change event copy & paste
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 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Change event copy & paste
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 |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Change event copy & paste
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Worksheet Change Event - copy cell to another sheet | Excel Discussion (Misc queries) | |||
Data Change after Copy/Paste | Excel Discussion (Misc queries) | |||
event macros vs copy/paste | Excel Worksheet Functions | |||
Copy Sheets minus Worksheet Change Event code & Macro Buttons | Excel Programming | |||
Worksheet_Change Event - Macro kills copy and paste | Excel Programming |