'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,
 

Some videos you may like

Excel Facts

Can you sort left to right?
To sort left-to-right, use the Sort dialog box. Click Options. Choose "Sort left to right"

jasonb75

Well-known Member
Joined
Dec 30, 2008
Messages
10,824
Office Version
  1. 2019
Platform
  1. Windows
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

Legend
Joined
Jun 19, 2002
Messages
63,650
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

New Member
Joined
Dec 12, 2010
Messages
5

ADVERTISEMENT

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

Well-known Member
Joined
Dec 30, 2008
Messages
10,824
Office Version
  1. 2019
Platform
  1. Windows
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

New Member
Joined
Dec 12, 2010
Messages
5

ADVERTISEMENT

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
 

lipmesiter

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

Well-known Member
Joined
Dec 30, 2008
Messages
10,824
Office Version
  1. 2019
Platform
  1. Windows
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,108,655
Messages
5,524,136
Members
409,562
Latest member
meeranaskar

This Week's Hot Topics

Top