Help please with cut paste and formulas

[AmandaA]

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

Any help would be great

 

[GS]

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

 

[AmandaA]

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

;1613922']> I have a worksheet which when I have a duplicate entry,
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.discussio

 

[joeu2004]

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
MediaFire: http://www.mediafire.com
FileFactory: http://www.filefactory.com
FileSavr: http://www.filesavr.com
RapidShare: 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?

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.

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.

 

[AmandaA]

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 an
formulas on row, which should now be 0 still have values.

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

I hope all this makes sense.

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

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 Exce
file
that you uploaded to a file-sharing website. ExcelBanter might provid
a
mechanism for doing this. Alternatively, use one of the following fre

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 yo
doing?

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 clarif
what
you are doing per above and resolve the #REF error.


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 migh
use
paste-special-value-and-number-formats. But as the name implies, tha
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