'Copy & Paste Without Formatting' Macro runtime error 1004

lipmesiter

New Member
Joined
Dec 12, 2010
Messages
5
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,
 

Excel Facts

Repeat Last Command
Pressing F4 adds dollar signs when editing a formula. When not editing, F4 repeats last command.
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?
 
Upvote 0
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...
 
Upvote 0
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
 
Upvote 0
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.
 
Upvote 0
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
 
Upvote 0
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.
 
Upvote 0
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

Code:
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.
 
Upvote 0

Forum statistics

Threads
1,213,482
Messages
6,113,916
Members
448,533
Latest member
thietbibeboiwasaco

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top