Excel vba Pastespecial xlvalues


I have been trying to write a small sub that will take contents from the clipboard to an excel spreadsheet...I have two Workbooks open...I select the data i want to copy...hit Ctrl C...then go to the other workbook which i want to paste the values.  On this sheet I have a button that runs the following macro...It worked earlier today but now every time i run it it hangs on the pastespecial line with the following error.  "Runtime Error '1004' Pastespecial method of range class failed.  I have tried every example i can find on the internet and nothing will work.  Any ideas?

Sub ImportData()

ActiveCell.PasteSpecialxlPasteValues , xlPasteSpecialOperationNone

Open in new window

Who is Participating?

[Webinar] Streamline your web hosting managementRegister Today

Tom UrtisConnect With a Mentor Commented:
It sounds like you are attempting to copy data from a workbook in one instance of Excel, and pasting it into another instance of Excel.   You probably had both workbooks open in the same instance of Excel when this worked for you.   The question is why you need tow separate instances, or can you just use one, seeing as you have used only one before when it was working.   There is code to handle transfer of data between instances but you probably don't need to do that.
Tom UrtisCommented:
By chance is the destination worksheet protected, or are there any merged cells in association with A12.
Change this:

ActiveCell.PasteSpecialxlPasteValues , xlPasteSpecialOperationNone

to this:

ActiveCell.PasteSpecial paste:=xlValues, operation:=xlPasteSpecialOperationNone

ToddHawley4984Author Commented:
Tom_Urtis: No to both questions.

Jppinto:  Same result with your code..

This may be of help....after the code fails if i exit and try to manually pastespecial i get the dialog asking if i want to paste as HTML, Unicode Text, Text....If i try this before running to code i can pastespecial just fine....that help?
ToddHawley4984Author Commented:
I opened the two spreadsheets in separate instances and it works fine...thanks
All Courses

From novice to tech pro — start learning today.