Error 1004 - PasteSpecial failure

[Otto Moehrbach]

Excel XP & 2003
I have a lot of VBA in a file that is being used by a dozen or so offices
throughout the state, one computer per office.
One, and only one, computer is getting a consistent error on the statement:
Dest.PasteSpecial xlPasteValues
I had them insert MsgBox's to determine that "Dest" is in fact a valid cell.
I had them remove and retype that one line just in case some invalid
characters had snuck into the code somehow. I validated every character in
that line to ensure the spelling and syntax is correct. I had another
office send them their file which is working fine, and that file produced
the same error in that one problem office.
Question: Why is this happening and what can be done to correct this?
Thanks for your time. Otto

 

[Jim Thomlinson]

Hard to say what is going on without seeing more code. Assuing that your
destination is valid then the possibilities are:

Your copied cells area is larger or smaller than your destination. Copied 5
cells and your destination is only 3 cells.

Somehow the copied cells are no longer copied so your paste fails as there
is nothing to paste.

Like I said if we could see more code it would be easier to diagnose.

As a complete aside usually better than pasting values is to just set the
values equal
dest.Value = range("A1").value
To do this the two ranges must be equal in size.

 

[Otto Moehrbach]

Thanks Jim
Remember that this same code is working on every one of about a dozen
other computers in the state. The copied range is 2 cells and the
destination cell is one cell. The complete Copy/Paste code is:
GreenButton.Offset(, 1).Resize(, 2).Copy
Dest.PasteSpecial xlPasteValues
"GreenButton" is one cell.
"Dest" is one cell.
These 2 lines of code are together as shown so nothing is happening in
between the copy and paste commands to clear the clipboard buffer.
I seem to remember that there is something about a reference that can upset
a VBA paste command but I don't remember more than that. BTW, I checked and
all the VBA references are the same in all offices.
Thanks for your time, Jim. Otto