Not good at explain I know what I want but to try to convey in words get
complex.
We are together about trigger from cell in column 'J' If dollar amount in'J'
is over 10.00 then copy data e to donors a data f don b data g don c data h
to don d data I to don e data j less 10.00 to don f data k don G
dont know how i got the A column mixed in my thoughts. Hope this makes sense
Thanks Much

"Carl Hartness" wrote:

Hi Curt,

CopyStuff has a couple of options, with and without target.value.
Delete the one you don't want.

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal target As
Range)
Select Case Sh.Name
Case "Data", "Motorcycle", "Indian", "Native"
'1=data 6=Motorcycle 7=Indian 8=Native nbrs
If target.Column = 10 And target.Value 10 Then _
Call CopyStuff(target)
End Select
End Sub
Public Sub CopyStuff(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)
' recommend disabling events to block extra passes through
' Workbook_SheetChange caused by changing Donors cells
Application.EnableEvents = False

' option 1, if you want value from col J in column A?
target.Copy Destination:=rngPaste
Range(target.Offset(0, -5), target.Offset(0, 2)).Copy
Destination:=rngPaste.Offset(0, 1)
rngPaste.Offset(0, 6) = target - 10

' option 2, next row down for demo purposes
Set rngPaste = rngPaste.Offset(1, 0)
rngPaste = Range(target.Offset(0, -5), target.Offset(0, 2))
Range(target.Offset(0, -5), target.Offset(0, 2)).Copy
Destination:=rngPaste
rngPaste.Offset(0, 5) = target - 10

Application.EnableEvents = True
End Sub

Carl
On Feb 28, 11:33 pm, Curt wrote:
What I am trying to do is a workbookevent. It is to fire when entry in column
'J' is entered Then to subtract 10.00 from entry and copy the entrys in cells
'E' 'F''G''H''I
'J-10.00' K'&'L of same row to next open row in donors worksheet or it may
insert a row. I have the first row on all sheets frozen for scrolling reasons
and labels. I am suprised that I've got this far. Now when entry is made
error workbook sub in yellow
select case in blue
in my code
will insert my code here
Thanks this old dog learning new tricks
Resources as you are a Blessing

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal target As Range)
SelectCase Sh.Name
Case "Data", "Motorcycle", "Indian", "Native"
'1=data 6=Motorcycle 7=Indian 8=Native nbrs
If target.Column = 10 And target.Value 10 Then _
Call .CopyStuff(target)
End Select
End Sub

I know the offsets are not right in this not sure of the rows count etc.
Public Sub CopyStuff(ByVal target As Range)
Dim wksSummary As Worksheet
Dim rngPaste As Range
Set wksSummary = Sheets("Donors")
Set rngPaste = wksSummary.Cells(Rows.Count, "A").End(xlUp).Offset(1, 0)
rngPaste = target.Value
rngPaste.Offset(0, 1) = target.Offset(0, -1)
End Sub

Finally Thanks Again



"Jim Cone" wrote:

Your explanation was not clear to me.
I've found that if you can describe exactly what you want to do
then writing the code becomes much easier.
Here is my interpretation. The code goes in the ThisWorkbook module.
--
Jim Cone
San Francisco, USA
http://www.realezsites.com/bus/primitivesoftware

Private Sub Workbook_SheetChange(ByVal Sh As Object, _
ByVal Target As Excel.Range)
Dim dblValue As Double
Dim varTargetAmt As Variant
varTargetAmt = Target.Cells(1, 1).Value

Select Case Sh.Name
Case "Data"
If Target.Column = 1 And varTargetAmt 10 Then
dblValue = varTargetAmt - 10
End If
Case "Motorcycle"
If Target.Column = 6 And varTargetAmt 10 Then
dblValue = varTargetAmt - 10
End If
Case "Indian"
If Target.Column = 7 And varTargetAmt 10 Then
dblValue = varTargetAmt - 10
End If
Case "Native"
If Target.Column = 8 And varTargetAmt 10 Then
dblValue = varTargetAmt - 10
End If
End Select
If dblValue 0 Then
Me.Worksheets("doner").Range("E2:I2").Value = _
Array(dblValue, 10, 20, 30, 40)
End If
End Sub
'-----------

"Curt"
wrote in message
I am missing something and am stumped. Have the following code in the
workbook I want it to run when an entry is made in column 'J' or '10'
reference Then subtract
10.00 amd take the remaining amount and place this and other data in row in
columns 'E F G H I' into donors worksheet. This is my first attempt at this
event happening. Anyway here is code
Any takers?
Thanks
Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal target As Range)
Select Case Sh.Name
Case "Data", "Motorcycle", "Indian", "Native"
'1=Data 6=Motorcycle 7=Indian 8=Native
If target.Column = 10 And target.Value 10 Then Call _
CopyStuff(target)
End Select
End Sub- Hide quoted text -

- Show quoted text -