I have a worksheet which when I have a duplicate entry, I cut and paste the needed information to the original. However, when I do this, the formula of the orginal then changes and has #ref in it. How do I prevent this?
Also, is there a way that I could use a macro to then go to the item I just copied and paste and delete that row.

Any help would be great.

I have a worksheet which when I have a duplicate entry, I cut and
paste the needed information to the original. However, when I do
this, the formula of the orginal then changes and has #ref in it.
How do I prevent this?
Also, is there a way that I could use a macro to then go to the item
I just copied and paste and delete that row.

Any help would be great.

Di you try Copy/Paste Values?

--
Garry

Free usenet access at http://www.eternal-september.org
Classic VB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion

Yes I have although at times, it looses the formating of the cells which it is going too. It also leaves the values at the cells I moving.

"AmandaA" wrote:
I have a worksheet which when I have a duplicate entry,
I cut and paste the needed information to the original.
However, when I do this, the formula of the orginal then
changes and has #ref in it. How do I prevent this?

Please provide some details. For example:
1. The cell name of original formula.
2. The original formula.
3. The cell name where your are pasting the formula.
4. The precise keystrokes to accomplish the "cut and paste".
5. The contents as they appear in both the original cell (#1)
and the new cell (#3).
6. Are either or both the original cell (#1) or the new cell (#2)
part of a table?

In addition, it might help if you provided the URL of an example Excel file
that you uploaded to a file-sharing website. ExcelBanter might provide a
mechanism for doing this. Alternatively, use one of the following free
file-sharing websites or your own.

Box.Net: http://www.box.net/files
Windows Live Skydrive: http://skydrive.live.com
MediaFi http://www.mediafire.com
FileFactory: http://www.filefactory.com
FileSavr: http://www.filesavr.com
RapidSha http://www.rapidshare.com

Normally, __cut__ and paste does not alter the formula.

But below, you use the term __copy__ and paste. Which one are you doing?


"AmandaA" wrote:
Also, is there a way that I could use a macro to then
go to the item I just copied and paste and delete that row.

Yes; it is called an event macro.

But before we complicate things any more, I suggest that you clarify what
you are doing per above and resolve the #REF error.


"AmandaA" wrote later in response to GS's
question about copy-and-paste-special-value:
Yes I have although at times, it looses the formating of the
cells which it is going too. It also leaves the values at
the cells I moving.

Copy-and-paste-special-value per se never copies the format. You might use
paste-special-value-and-number-formats. But as the name implies, that does
not copy __all__ formatting information; for example, not Conditional
Formatting.

Also, does the original cell have values, as you say here, or formulas, as
you said initially?

Normally, paste-special-value does not alter the original cell. In
particular, it would not change formulas to values in the __original__ cell.

Here is a link to a sample of the file: http://sdrv.ms/1brMBIy.

Here is what I'm doing:

1. Select the columns under 1941 AC4:AH4
2. Then ctrlX to move values to AC3:AH3
3. Values move and delete, but the formulas on row 3 Now have #ref and formulas on row, which should now be 0 still have values.

If I use ctrlv cells move and formulas don't change, but values remain on row 4, which it should, but I don't need it anymore as I will want to delete this row. Which is why I asked about a macro do do all this with a single/combo keystroke.

I hope all this makes sense.

The sample spreadsheet has the formulas that I use for the first 40 rows. My main file has over 1000 rows.