PasteSpecial macro

[Guest]

I have been using the macro below to paste values copied to the clipboard in Excel 2000. For example, I will use the Excel copy command to copy the value of a cell (or a range of cells), then click on another cell in either the same worksheet or another worksheet in the same workbook. Then, I would run the macro to paste special only the values copied leaving the cell only the values pasted while persreving the existing cell formats. This macro has worked for me for 2 years running Excel 2000 under Windows 2000 operating system. I have recently purchased a new computer with the Windows XP operating system and am no longer able to run this macro. Every time it is run, I receive the following error message

Run-time error '1004'

PasteSpecial method of Range class faile

This seems like it should be simple to do, but I am stumped. Thanks for any help you can provide

Sub paste_values(

' paste_values Macr

Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:=
False, Transpose:=Fals
End Su

 

[Bob Flanagan]

Normally you would get that error if there is nothing copied. Are you sure
you are not doing something that clears the copy buffer? If you put a STOP
statement right before it, you can then go to a worksheet and see if you can
manually do a paste or a pastespecial to confirm there is still something in
the copy buffer

Bob Flanagan
Macro Systems
http://www.add-ins.com
Productivity add-ins and downloadable books on VB macros for Excel

I have been using the macro below to paste values copied to the clipboard

in Excel 2000. For example, I will use the Excel copy command to copy the
value of a cell (or a range of cells), then click on another cell in either
the same worksheet or another worksheet in the same workbook. Then, I would
run the macro to paste special only the values copied leaving the cell only
the values pasted while persreving the existing cell formats. This macro
has worked for me for 2 years running Excel 2000 under Windows 2000
operating system. I have recently purchased a new computer with the Windows
XP operating system and am no longer able to run this macro. Every time it
is run, I receive the following error message:

 

[Guest]

I can do a manual paste & it works fine. Is there something in Windows XP that is different that would cause this. We have 10 computers in the office, 8 running XP & 2 running Windows 2000. All XP machines produce this error message. The macro runs fine on the 2000 machines

I've searched various settings for something to work, but am completely stumped.

 

[Doug Glancy]

Don't know if you've solved this yet. I had the same result as you had when
I pasted the code from your message into a macro. Running Win XP and XL2k I
also got the Runtime 1004 message.

I then used the macro recorder to record the action of paste special -
values. The recorded macro looks identical as far as I can see, but it
works.

hth,

Doug

I have been using the macro below to paste values copied to the clipboard

in Excel 2000. For example, I will use the Excel copy command to copy the
value of a cell (or a range of cells), then click on another cell in either
the same worksheet or another worksheet in the same workbook. Then, I would
run the macro to paste special only the values copied leaving the cell only
the values pasted while persreving the existing cell formats. This macro
has worked for me for 2 years running Excel 2000 under Windows 2000
operating system. I have recently purchased a new computer with the Windows
XP operating system and am no longer able to run this macro. Every time it
is run, I receive the following error message:

 

[Guest]

Thanks. I originally created the macro using the recorder. So, I tried it again. If I assign the macro a keyboard shortcut, it works using that, but doesn't work if attempting to run using Tools|Macro\Run. Go figure.

 

[Doug Glancy]

When you go into Tools|Macro you'll see that the cell you're copying loses
the "marching ants" border that shows it's ready to be copied. The status
bar message "Select Destination and press ENTER or choose Paste" also goes
away. In other words your no longer in copy/paste mode and when you try to
Run the macro, it's not finding anything to paste.

Would you actually want to run it that way? If so, why? It's easier to
just right-click, choose PasteSpecial and then Values, or to use your
keyboard shortcut.

hth,

Doug

Thanks. I originally created the macro using the recorder. So, I tried

it again. If I assign the macro a keyboard shortcut, it works using that,
but doesn't work if attempting to run using Tools|Macro\Run. Go figure.