anyone see why this creats this error
Thanking you in advance

If target.Column = 10 And target.Value 10 Then

Private Sub Workbook_SheetChange(ByVal sh As Object, ByVal target As Range)
Dim wksSummary As Worksheet
Dim rngPaste 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(sh, target)
End If
End Select
End Sub

If there's an error in the changed range, then target.value will cause an error.

If you change more than one cell, then target.value can't be compared to a
single number.



Curt wrote:

anyone see why this creats this error
Thanking you in advance

If target.Column = 10 And target.Value 10 Then

Private Sub Workbook_SheetChange(ByVal sh As Object, ByVal target As Range)
Dim wksSummary As Worksheet
Dim rngPaste 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(sh, target)
End If
End Select
End Sub

--

Dave Peterson

The code you have posted is fine. This works for me... (I don't have CopyStuff)

Private Sub Workbook_SheetChange(ByVal sh As Object, ByVal target As Range)
Dim wksSummary As Worksheet
Dim rngPaste 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
MsgBox sh.Name & " " & target.Value
End If
End Select
End Sub

I suspect that you have declared CopyStuff incorrectly. It should be
something like

Public Sub CopyStuff (byval sh as Worksheet, byval target as Range)
--
HTH...

Jim Thomlinson


"Curt" wrote:

anyone see why this creats this error
Thanking you in advance

If target.Column = 10 And target.Value 10 Then

Private Sub Workbook_SheetChange(ByVal sh As Object, ByVal target As Range)
Dim wksSummary As Worksheet
Dim rngPaste 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(sh, target)
End If
End Select
End Sub

I suspect it works if 'target' is one cell, but if 'target' is more
than one cell, it doesn't. A collection of cells has no Value
property; only individual cells do.

Hth,
merjet

Dim myArr as variant
myArr = activesheet.range("a1:c9").value
works ok.

merjet wrote:

I suspect it works if 'target' is one cell, but if 'target' is more
than one cell, it doesn't. A collection of cells has no Value
property; only individual cells do.

Hth,
merjet

--

Dave Peterson

Will enclose copystuff code

"Jim Thomlinson" wrote:

The code you have posted is fine. This works for me... (I don't have CopyStuff)

Private Sub Workbook_SheetChange(ByVal sh As Object, ByVal target As Range)
Dim wksSummary As Worksheet
Dim rngPaste 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
MsgBox sh.Name & " " & target.Value
End If
End Select
End Sub

I suspect that you have declared CopyStuff incorrectly. It should be
something like

Public Sub CopyStuff (byval sh as Worksheet, byval target as Range)

Public Sub CopyStuff(ByVal Sh As Worksheet, 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


--
HTH...

Jim Thomlinson


"Curt" wrote:

anyone see why this creats this error
Thanking you in advance

If target.Column = 10 And target.Value 10 Then

Private Sub Workbook_SheetChange(ByVal sh As Object, ByVal target As Range)
Dim wksSummary As Worksheet
Dim rngPaste 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(sh, target)
End If
End Select
End Sub

here is complete code of this novice Want entry in any cell in column 'J'
over $10.00 to trigger this code.Column 'J' is only column set to currency.
Donors sheet that data will transfer to has first row frozen for labels &
scrolling. Do we need to insert a row to paste ?
Thanks much for your help

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal target As Range)
'Dim wksSummary As Worksheet
'Dim rngPaste 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(Sh, target)
End If
End Select
Public Sub CopyStuff(ByVal Sh As Worksheet, 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





"Dave Peterson" wrote:

Dim myArr as variant
myArr = activesheet.range("a1:c9").value
works ok.

merjet wrote:

I suspect it works if 'target' is one cell, but if 'target' is more
than one cell, it doesn't. A collection of cells has no Value
property; only individual cells do.

Hth,
merjet

--

Dave Peterson

Try something more like...

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal target As Range)
'Dim wksSummary As Worksheet
'Dim rngPaste As Range
If target.Cells.Count < 1 Then Exit Sub
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 If
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(Rows.Count, "A").End(xlUp).Offset(1, 0)

rngPaste.Value = target.Value
rngPaste.Offset(0, 1) = target.Offset(0, -1)
End Sub
--
HTH...

Jim Thomlinson


"Curt" wrote:

here is complete code of this novice Want entry in any cell in column 'J'
over $10.00 to trigger this code.Column 'J' is only column set to currency.
Donors sheet that data will transfer to has first row frozen for labels &
scrolling. Do we need to insert a row to paste ?
Thanks much for your help

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal target As Range)
'Dim wksSummary As Worksheet
'Dim rngPaste 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(Sh, target)
End If
End Select
Public Sub CopyStuff(ByVal Sh As Worksheet, 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





"Dave Peterson" wrote:

Dim myArr as variant
myArr = activesheet.range("a1:c9").value
works ok.

merjet wrote:

I suspect it works if 'target' is one cell, but if 'target' is more
than one cell, it doesn't. A collection of cells has no Value
property; only individual cells do.

Hth,
merjet

--

Dave Peterson

Thanks much Jim will see what I can do.
Thanks Again

"Jim Thomlinson" wrote:

Try something more like...

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal target As Range)
'Dim wksSummary As Worksheet
'Dim rngPaste As Range
If target.Cells.Count < 1 Then Exit Sub
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 If
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(Rows.Count, "A").End(xlUp).Offset(1, 0)

rngPaste.Value = target.Value
rngPaste.Offset(0, 1) = target.Offset(0, -1)
End Sub
--
HTH...

Jim Thomlinson


"Curt" wrote:

here is complete code of this novice Want entry in any cell in column 'J'
over $10.00 to trigger this code.Column 'J' is only column set to currency.
Donors sheet that data will transfer to has first row frozen for labels &
scrolling. Do we need to insert a row to paste ?
Thanks much for your help

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal target As Range)
'Dim wksSummary As Worksheet
'Dim rngPaste 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(Sh, target)
End If
End Select
Public Sub CopyStuff(ByVal Sh As Worksheet, 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





"Dave Peterson" wrote:

Dim myArr as variant
myArr = activesheet.range("a1:c9").value
works ok.

merjet wrote:

I suspect it works if 'target' is one cell, but if 'target' is more
than one cell, it doesn't. A collection of cells has no Value
property; only individual cells do.

Hth,
merjet

--

Dave Peterson

end if befor end select
error end if without block if
' use comment on end if an no responce from code?
WOW
copied code comp[letely as posted

"Jim Thomlinson" wrote:

Try something more like...

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal target As Range)
'Dim wksSummary As Worksheet
'Dim rngPaste As Range
If target.Cells.Count < 1 Then Exit Sub
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 If
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(Rows.Count, "A").End(xlUp).Offset(1, 0)

rngPaste.Value = target.Value
rngPaste.Offset(0, 1) = target.Offset(0, -1)
End Sub
--
HTH...

Jim Thomlinson


"Curt" wrote:

here is complete code of this novice Want entry in any cell in column 'J'
over $10.00 to trigger this code.Column 'J' is only column set to currency.
Donors sheet that data will transfer to has first row frozen for labels &
scrolling. Do we need to insert a row to paste ?
Thanks much for your help

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal target As Range)
'Dim wksSummary As Worksheet
'Dim rngPaste 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(Sh, target)
End If
End Select
Public Sub CopyStuff(ByVal Sh As Worksheet, 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





"Dave Peterson" wrote:

Dim myArr as variant
myArr = activesheet.range("a1:c9").value
works ok.

merjet wrote:

I suspect it works if 'target' is one cell, but if 'target' is more
than one cell, it doesn't. A collection of cells has no Value
property; only individual cells do.

Hth,
merjet

--

Dave Peterson