If all you're interested in is the value, you could just assign the values to
the other cells.

I just kept the bottom portion that needed changing:

On Error GoTo errHandler:
Application.EnableEvents = False
destCell.Resize(1, 9).Value _
= Target.EntireRow.Resize(1, 9).Value
Target.Value = "Copied"
Beep

errHandler:
Application.EnableEvents = True

End Sub


or if you want to paste values:


On Error GoTo errHandler:
Application.EnableEvents = False
Target.EntireRow.Resize(1, 9).Copy
destCell.PasteSpecial Paste:=xlPasteValues
Target.Value = "Copied"
Application.CutCopyMode = False
Beep

errHandler:
Application.EnableEvents = True

End Sub



Andre Croteau wrote:

Hi Again Dave,

I believe this must be close to the record number of threads....

I have a final(?) question regarding the original code:

The original code works well, but I would like to copy/PasteSpecialValues
from any of the 3 sheets to the "Summary" sheet

Your code has a straight "Copy / Paste" procedures, so that formulas are
also copied, which is not always the best thing to do.

Is there one last line code you can change/modify?

Thanks in advance, and also for the answer to my other question

André

"Dave Peterson" wrote in message
...
PMFJI,

the problem I have with stuff like this is that I don't know when to do
the
copy.

You could do the copy when you finish the entry in column I (whatever
finish
means!).

Or maybe use column J as an indicator. That would give you a chance to
correct
any typos in A:I without having go to the other sheet to fix it, too.

I'm gonna use column J, but you could use column I if you really want.

This works for me, but with my typing, I'm not sure if it would make my
job
easier or more difficult:

Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)

Dim destCell As Range

'one cell at a time only!
If Target.Cells.Count 1 Then Exit Sub

'only check column J
If Intersect(Target, Me.Range("J:J")) Is Nothing Then Exit Sub

'and it can't be empty!
If IsEmpty(Target) Then Exit Sub

'Column A of the row must have data
If IsEmpty(Me.Cells(Target.Row, "A")) Then
MsgBox "Please put something in A" & Target.Row
Exit Sub
End If

With Worksheets("summary")
Set destCell = .Cells(.Rows.Count, "A").End(xlUp).Offset(1, 0)
End With

On Error GoTo errHandler:
Application.EnableEvents = False
Target.EntireRow.Resize(1, 9).Copy _
Destination:=destCell
Target.Value = "Copied"
Beep

errHandler:
Application.EnableEvents = True

End Sub

Nadia wrote:

Me AGAIN...
Ive copied and made changes to the IF statement so that data from A:I in
"sheet 1" also appears in the "Summary" sheet A:I... however, if one of
the
cells in "sheet 1" is blank the rest of the data jumps up 1 row in the
"summary" sheet.. how can I fix this.
cheers,
Nadia

"Nadia" wrote:

Hi Gord,
The code works great..... NOW... how can I amend this to get data from
more
than 1 cell on the same row to do the same, e.g. I want to type data
in
colums A:I.
many thanx


"Gord Dibben" wrote:

Andre

Stick this code in your 3 worksheets.

Private Sub Worksheet_Change(ByVal Target As Range)
''when entering data in a cell in Col A
On Error GoTo enditall
Application.EnableEvents = False
If Target.Cells.Column = 1 Then
n = Target.Row
If Excel.Range("A" & n).Value < "" Then
Excel.Range("A" & n).Copy Destination:= _
Sheets("Summary").Cells(Rows.Count, 1).End(xlUp) _
.Offset(1, 0)
End If
End If
enditall:
Application.EnableEvents = True
End Sub


Gord Dibben Excel MVP

On Wed, 15 Dec 2004 18:58:50 GMT, "Andre Croteau"

wrote:

Hello Frank,


I don't know about you Nadia, but I sure would like to know!

I still think these newsgroups are the best learning tool!!!., and
the best
thing since sliced bread!!

Thank you in advance!!!!! and Happy Holidays to all!

André


"Frank Kabel" wrote in message
...
Hi
this would be only possible using vBA event procedures (e.g.
worksheet
change event handlers). Do you want to go this way?

--
Regards
Frank Kabel
Frankfurt, Germany

Nadia wrote:
I would like to enter data into lists on 3 different sheets and
I
would like that data to automatically collate on a summary
sheet
(i.e. as soon as I hit enter after typing the data in one of
the 3
sheets it should appear on the summary sheet on the next
available
row).
Can this be done??






--

Dave Peterson

--

Dave Peterson