workbook event
 

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

workbook event
 

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

--
HTH...

Jim Thomlinson


"Curt" wrote:

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

workbook event
 

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

"Jim Thomlinson" wrote:

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

--
HTH...

Jim Thomlinson


"Curt" wrote:

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