workbook event

C

Curt

have following code working with two problems. (1) copys more columns than I
want only want to copy (E) thru (L) do not want (M) (2) when trigger cell
in data worksheet is changed code inserts a new line. Wud like to only have
code only make change in target cell that was changed. Not start a new entry.

Help is greatly appreciated
Thanks

Public Sub CopyDonors(ByVal target As Range)
Dim wksSummary As Worksheet
Dim rngPaste As Range
Set wksSummary = Sheets("Donors")
Set rngPaste = wksSummary.Cells(65536, "A").End(xlUp).Offset(1, 0)
' disabling events to block extra passes through
' Workbook_SheetChange caused by changing Donors cells
Application.EnableEvents = False
Set rngPaste = rngPaste.Offset(1, 0)
rngPaste = Range(target.Offset(0, -7), target.Offset(0, 2))
Range(target.Offset(0, -7), target.Offset(0, 2)).Copy _
Destination:=rngPaste
rngPaste.Offset(0, 7) = target - 10
Application.EnableEvents = True
End Sub
 
J

Jim Thomlinson

A little confused exactly what you want. Where should the paste occure? You
are passing in a target range and you want to copy some cells based on the
location of that range. Where are the coppied cells supposed to be pasted...
As for part 1 of your question give this a whirl...

Public Sub CopyDonors(ByVal target As Range)
Dim wksSummary As Worksheet
Dim rngPaste As Range
Set wksSummary = Sheets("Donors")
Set rngPaste = wksSummary.Cells(65536, "A").End(xlUp).Offset(1, 0)
' disabling events to block extra passes through
' Workbook_SheetChange caused by changing Donors cells
Application.EnableEvents = False
Set rngPaste = rngPaste.Offset(1, 0) 'Why move down again?
rngPaste = Range(target.Offset(0, -7), target.Offset(0, 2)) 'What is
this?
Range(target.Offset(0, -7), target.Offset(0, 1)).Copy _
Destination:=rngPaste 'Change 2 to 1
rngPaste.Offset(0, 7) = target - 10
Application.EnableEvents = True
End Sub
 
C

Curt

With your help all is working fine on this part now. Trying to figure out to
have multiple conditions in row to trigger event. Hope I suceed. Still
learning
Thanks
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Condition 4
Trigger Question 2
copy certain cells 1
triogger reset 0
offset copy 4
Application Defined or object defined error..?Need help generic er 5
Deleting specific information 1
Trigger or code 0

Top