I can only agree with Gord's comments.

But it is better to use the trigger rather than the isblank and the copy
paste special.

The copy paste special gets messy, using the worksheet_change event in the
worksheet where the data will be entered, not the one where it is to be
copied.

HTH.


--
Hope this helps
Martin Fishlock, Bangkok, Thailand
Please do not forget to rate this reply.


"Gord Dibben" wrote:

Yes

If using Martin's #1. suggestion.

Convert the formulas on sheet2 to values before you delete sheet1

Copy then EditSpecialPaste Special(in place)ValuesOKEsc

If using a macro as in #2 suggestion, the cells are already values so go ahead
and delete sheet1


Gord Dibben MS Excel MVP

On Tue, 4 Mar 2008 15:12:00 -0800, Richard
wrote:

Is there a way to keep the contents on sheet2 after the contents are deleted
from sheet1

"Martin Fishlock" wrote:

Richard:

You can't use a worksheet function to copy the contents from one sheet to
another sheet.

But there are some options:

1. Using =if(isblank(sheet1!a1),"",sheet1!a1)
2. Using a macro that copies the data using a trigger when the cell value is
changed. Have a look at David McRitchie's site for some ideas:
http://www.mvps.org/dmcritchie/excel/event.htm#change.




--
Hope this helps
Martin Fishlock, Bangkok, Thailand
Please do not forget to rate this reply.


"Richard" wrote:

Using a worksheet function how would you copy the contents from say sheet 1
to sheet 2. For example: Sheet1 has a part number in Column a, A qty in
Column b, a date in Column c. Each time a part number, qty and date is
entered paste it to sheet 2 and keep going down each row wherever a new
number is entered. Thanks in advance!