Paste-As-Text macro

[paulkaye]

I regularly need to copy and paste-as-text (rather than pasting formatting/html, etc.). In MS Word, I use the following macro:

Sub PasteSpecial()
Selection.PasteSpecial DataType:=wdPasteText
End Sub

but I get a runtime error when I try to use this in Excel. I assume there's something fundamentally different that I don't understand between the programs. At the moment I use ctrl+alt+v to bring up the paste special dialogue box, but I'd like to be able to just ctrl+shift+v to paste as plain text.
Any advice would be most appreciated.


Paul

[Wries]

Hello paulkaye,
please try
ActiveCell.PasteSpecial paste:=xlPasteValues

Kind regards,
Wries

[paulkaye]

I got:
Runtime error '1004': PasteSpecial method of Range class failed

I am often copying from outside of Excel - could it be that paste>values is a function only for pasting from copied Excel cells?

[Wries]

sorry, I haven't realized you are copying outside excel.

Please try again with this and advise if it is working from your end.

Sub Paste()
ActiveCell.PasteSpecial xlPasteAll
End Sub

Kind regards,
Wries

[paulkaye]

Thanks - that worked perfectly, except for one really strange problem. After I paste, undo no longer works. That is, all the 'stored' steps to which I can normally 'undo', disappear. I don't just mean that I can't undo the paste performed by the macro; I mean that all previous steps disappear from the undo list, too!

[Wries]

Seems normal to me. I have never been able to use undo button after running a macro.

There are some links in google regarding this issue. As far as I have read the "undo cache" is cleared after/before macro is used.
Try to google for undo macro. I have seen some, but I have no use for them so far.

Kind regards,
Wries

[macropod]

Any running of a macro in Excel clears the Undo stack.

[paulkaye]

Wow - I've never heard of such a thing. Doesn't happen in Word, so why should it be necessary in Excel? I looked around as you suggested, and I saw the problem described in several places - and no solution. This actually means that I will not use the macro at all - no way I can risk ruining a spreadsheet for such a simple action. Oh well, thanks for your help anyway - I appreciate it.

[macropod]

All Excel versions are the same in that respect, so I imagine it's a design limitation.