'Copy & Paste Without Formatting' Macro runtime error 1004

[lipmesiter]

Hi and thanks for all your help in advance.

I created a macro with CTRL-V shortcut to copy text from websites and PDF's and paste them directly into Excel without formatting - just as text. Here is the VBA script and it works perfectly;


Sub PasteWOFormatting()
'
' PasteWOFormatting Macro
' Press Ctrl+v to paste text into a cell without any formatting
'
' Keyboard Shortcut: Ctrl+v
'
ActiveSheet.PasteSpecial Format:="Text", Link:=False, DisplayAsIcon:= _
False
End Sub


HOWEVER, whenever I copy and then try to paste something within Excel, I get;
'Runtime error 1004 - PasteSpecial Method of Worksheet class failed'

I think its got something to do with the clipboard and I have searched all over the web to try and find a solution but nothing works. I am not much good with VBA and would love a simple solution such as an IF THEN function in my VBA script or something else that allows me to copy within Excel as well.

Thank you so much for your time,

 

[VoG]

Try assigning a different shortcut.

 

[jasonb75]

that was my first thought Peter, I've just tried it with the ctrl V shortcut and it doesn't fail. could it be the format of the source data not being recognised as text?

 

[VoG]

I guess it depends how you copy and paste. I tend to use CTRL + C and CTRL + V in which case that error is generated. I changed the shortcut to CTRL + D and no error...

 

[lipmesiter]

Thanks Peter and Jason for the replies,

Yes, creating a different shortcut is the obvious solution... but this is a file that a number of people in different locations around the world are using to compile a massive spreadsheet from website/PDF sources, and asking them to use E.g. CTRL-S when copying from outside sources and CTRL-V when copying from within excel may be beyond them. It would be easier if they just has one shortcut for all copy tasks.

Thanks for all your assistance.
Jon

 

[jasonb75]

Strange, I just tried ctrl c ctrl v to copy paste this page and no error, i don't use pdf's though so don't have anything to test on there.

 

[lipmesiter]

Hi Jason,

Copying from anywhere to Excel works fine.

Copying from within Excel and pasting to another cell within excel creates the error.

Any ideas for a VBA script as I have tried everything?

Thanks so much

 

[VoG]

Cross posted http://www.excelforum.com/excel-pro...d-paste-without-formatting-macro-problem.html

 

[lipmesiter]

Sorry I didn't realize that these excel forums were in anyway related. I won't crosspost next time. Any VBA solutions to my error?

Thanks in advance.

 

[jasonb75]

There's nothing wrong with cross posting, so long as it's done in the right way. see http://www.excelguru.ca/node/7 for more info on cross posting.

How about

Sub PasteWOFormatting()
'
' PasteWOFormatting Macro
' Press Ctrl+v to paste text into a cell without any formatting
'
' Keyboard Shortcut: Ctrl+v
'
On Error GoTo line1
ActiveSheet.PasteSpecial Format:="Text", Link:=False, DisplayAsIcon:= _
False
Exit Sub
line1:
ActiveSheet.Paste
End Sub

Should kick back to the regualr Ctrl V paste when the error occurs.