Copy contents to another sheet

[Richard]

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!

 

[Martin Fishlock]

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.

 

[Richard]

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

 

[Gord Dibben]

Yes

If using Martin's #1. suggestion.

Convert the formulas on sheet2 to values before you delete sheet1

Copy then Edit>Special>Paste Special(in place)>Values>OK>Esc

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


Gord Dibben MS Excel MVP

 

[Martin Fishlock]

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.