Excel Copy Paste Bug

[amb]

There is a bug in Excel that when you copy a block and then press a key
anywhere else or do anything, your data you placed in the clipboard is
overwritten with blank or nothing. This is very frustrating and seems to
have crept in in the last several versions of Excel. Is there any work
around?

 

[Bernie Deitrick]

No, there is no work-around. When you copy something, you need to
paste it before doing other work.

HTH,
Bernie
MS Excel MVP

 

[J.E. McGimpsey]

OTOH, you should not rely on this for applications which may be run
cross-platform. MacXL does not clear the clipboard, so you can paste
even if you explicitly set .CutCopyMode to False.

 

[amb]

..CutCopyMode to False?

Does this imply I can somehow turn this idiotic behaviour off?

 

[Bernie Deitrick]

No,

That clears the clipboard. Simply pasting doesn't clear the clipboard, so
some developers are in the habit of using that statement after any
copy/paste routine to ensure that there isn't any possibility of anything
getting pasted by mistake.

HTH,
Bernie
MS Excel MVP

 

[Dana DeLouis]

Are you able to use the "Office Clipboard?" In the latest versions of
Excel, if you hit Ctrl+C twice, this will bring up the Office Clipboard.
However, I don't believe there is any vba control of the office clipboard.

 

[amb]

It does - but the problem remains. It still clears. I have sent this bug
to Microsoft in the vain hope they will fix it.

 

[Dana DeLouis]

J.E. May I ask a question? This is for educational curiosity since I don't
own a Mac.
You say the MacXL does not "dump" the clipboard when Cut-Copy mode is turned
off. Are you able to copy "All" cells on a worksheet, turn off Cut-Copy
Mode, and then paste into another Workbook? To me, that sounds pretty
impressive. I think the Office Clipboard uses about 8 Megs for holding
roughly 4 columns, so a complete workbook seems like a lot of memory.
Again, just curious...

 

[J.E. McGimpsey]

Dana -

How much memory and HD space do you have? The MacOS System clipboard
isn't as parsimonious with memory as the Office Clipboard - its
memory demand appears to be substantially greater than what's
necessary to hold the cell info (even assuming 24 bytes per cell on
average). I have no idea what the internal structure of the
clipboard is, though I may go look it up in my copious free time.

But I just copied a million cells worth of constants, cleared the
CutCopyMode, created a new workbook and pasted the million cells
into it, without a lot of delay. Two million took a couple of
minutes overall. It appears that the time and Virtual Memory
requirements go up exponentially with the number of cells - trying
to copy 16 million cells ran me out of HD space with 1.6GB of
virtual memory swap pages taken up on my boot drive (more than twice
as much as my 768MB of RAM), and the process slowed to a crawl.

 

[Dana DeLouis]

Thanks J.E. for that information. Very interesting. I didn't know the
MaxOS did that. Just curious to learn, that's all.

... trying
to copy 16 million cells ran me out of HD space with 1.6GB

Looks to me that the two operating systems take different approaches. In
Windows, one can do the following in 0 seconds:

Cells.Copy

The only explanation I have for Excel being able to copy 16 Megs of cell
data is...it doesn't! In Windows, I "think" the statement "Copy Cells to
the clipboard" is not correct. I "think" it just sets a link to where the
data is and places the marching ants around it. Doing most items like the
OP is doing breaks the integrity of that link, so Excel breaks the link and
removes the marching ants. I don't think it is actually clearing 16+ Megs
of data. That's why I believe the hard drive light doesn't come on with
"Cells.Copy." (There was a way to trick the link I believe in Excel 97 so
when you pasted, the data was from the wrong cell link, but I don't have my
notes on that anymore..it was since fixed.)

To tell you the truth, I am a little surprised that Excel & MaxOs works like
it does. But.. oh well.

The reason Windows can run the following so quickly is that I believe it
does not copy all those cells to memory. It just sets an internal pointer
to where the data is. Anyway, interesting topic.

Sub Macro1()
[AA1:AZ60000].Copy
[A1:Z60000].PasteSpecial Operation:=xlAdd
Application.CutCopyMode = False
End Sub

There is no way that I know of to copy a number into the clipboard, and use
something like "PasteSpecial Operation:=xlAdd" I think it doesn't get it
from the clipboard, it gets the number/numbers from the marching ants.