Please Note:
This article is written for users of the following Microsoft Excel versions: 97, 2000, 2002, and 2003. If you are using a later version (Excel 2007 or later), this tip may not work for you. For a version of this tip written specifically for later versions of Excel, click here: Can't Copy Data between Workbooks.

Can't Copy Data between Workbooks

Neil has Excel installed on several PCs in his office. On most of these he can copy data from one workbook to another workbook with no problems. On one of the PCs, although he can copy data from one worksheet to another worksheet in the same workbook, Neil cannot copy data to a different workbook.

More than likely this has to do with how the two workbooks are opened on the problem machine. If you open the workbooks in two different instances of Excel, then copying and pasting between instances is not the same as when the workbooks are opened in the same instance. If you open the two workbooks in different instances of Excel, then when you go to paste information into the target workbook, you get the results of whatever formulas you are copying instead of the original formulas. (This is just one example; there are other pasting differences as well.)

The solution is to make sure that the workbooks are opened in the same instance of Excel. The easiest way to do this is to make sure that once the first workbook is open, you open the second workbook by using the Open tool on the toolbar or by choosing File | Open. If you instead use the Windows Start menu or a desktop icon to start Excel, you are opening a second instance of the program.

If this doesn't solve the problem for you, then you should make sure that there is no macro running in the target workbook or worksheet when it is activated. Look for code in the ThisWorkbook module and any other worksheet module in the target workbook's VBA project. If you do find a macro there, then it could be that the macro is altering what is in the Clipboard or clearing it out entirely. You'll need to do some detective work to figure out if this is the case.

ExcelTips is your source for cost-effective Microsoft Excel training.
This tip (3111) applies to Microsoft Excel 97, 2000, 2002, and 2003. You can find a version of this tip for the ribbon interface of Excel (Excel 2007 and later) here: Can't Copy Data between Workbooks.

Author Bio

Allen Wyatt

With more than 50 non-fiction books and numerous magazine articles to his credit, Allen Wyatt is an internationally recognized author. He is president of Sharon Parq Associates, a computer and publishing services company. Learn more about Allen...

MORE FROM ALLEN

Creating a String in a Macro

Need to put together a bunch of characters to create a text string? You can do it in your macros by using the String ...

Comprehensive VBA Guide Visual Basic for Applications (VBA) is the language used for writing macros in all Office programs. This complete guide shows both professionals and novices how to master VBA in order to customize the entire Office suite for their needs. Check out Mastering VBA for Office 2010 today!

More ExcelTips (menu)

Copying a Cell without Formatting

When you are copying a cell from one place to another (perhaps even to a different worksheet), you may not want to copy ...

Comments

If you would like to add an image to
your comment (not an avatar, but an image to help in making the point of your comment),
include the characters [{fig}] in your comment text. You’ll be prompted to upload
your image when you submit the comment. Maximum image size is 6Mpixels. Images larger than 600px wide or 1000px tall will
be reduced. Up to three images may be included in a comment. All images are subject to
review. Commenting privileges may be curtailed if inappropriate images are posted.

Source

(gif, jpeg or png only, 5MB maximum file size)

Source

(gif, jpeg or png only, 5MB maximum file size)

Source

(gif, jpeg or png only, 5MB maximum file size)

Notify me about new comments ONLY FOR THIS TIP

Notify me about new comments ANYWHERE ON THIS SITE

Hide my email address

What is six more than 8?

2017-10-17 15:07:15

Maria Silva

I'm using macros that you said on a previous article that would disable DragAndDrop for a Workbook and this is a must for the project, but copying and pasting from and to another workbooks is also a must. I work for a worldwide food company and the project will be used by up to 20 thousand people from several countries to open tickets. There's this excel workbook created by me that will have its content copied and pasted from other worksheets sent by different users is there a way to keep all these events in the same workbook or do I need to choose? The company used excel 2016 and the macros I copied from you are the ones below.

This advice resolved an issue a customer was experiencing, thanks, Steve.

2017-04-11 12:08:55

John Niket

Thank you! , it was the macro and specifically the ActiveWorkbook.UpdateLink on worksheets activate - cause the copy/paste failure,

2016-11-05 00:36:50

ramdas p v

Cant_Copy_Data_between_cells in Workbooks

2016-10-05 10:21:17

Carl

I have been looking for a solution for this for over a year, and this actually works!!!! Thank you very much!

2016-08-31 08:00:04

ronak

when i copy some word from another file and then paste in excel file but the word/number is can't copy the same in excel...kindly advice for the same..

2016-06-20 10:56:53

Kulbir Singh

Thanks a lot

i was working on two different workbooks in two different instances of Excel. That was the problem.

Thanks once again

2016-02-16 17:10:17

Walt McKee

Good tip, solved my problem. I had some Copy/Destination code in my worksheets that ran at sheet activation - and when I went to cut & paste data this was clearing the clipboard. I restricted when this code could run and it solved my problem (Excel 2007).

Thanks, Walt

2015-12-16 13:35:09

Lynn Foster

I use the option of multiple instances of excel frequently. Often I want the destination cell to only have the value as opposed to the formulas. If you have trouble copying cells from a workbook in one instance of excel to a workbook in a 2nd instance of excel: select cells in 1st instance..Copy...select destination cell in 2nd workbook....select drop down for Paste, then select the option for "Match Destination Formatting" or select Paste Special, then select text. this will paste the copied cells value into the destination cells.

2015-12-09 10:20:17

Nancy

I've been downloading income statements from Sage for years and copy/paste special value to my spreadsheet with no problem.suddenly, when ever I try to do this, it is pasting as an enbed object instead of values.how can I fix this??

Thank you by this tip!this solve a problem with copying sheets from different excel files.Carlosfrom Brazil

2015-08-03 11:20:16

David

Alan,

Do you know what has happened within Excel to eliminate this functionality? I have worked with Excel for years and copying from one instance of Excel to another instance of Excel has never been a problem until a couple of weeks ago? It's as if one instance of Excel no longer recognizes the other instance as Excel. Is this a bug? While I appreciate all of the work-arounds mentioned in this topic string, I am more interested in getting this resolved so I can continue to use Excel efficiently like I have in the past. In addition, I have been using Excel 2010 in this manner for several years now! Please advise if you know what has caused this issue and how to correct.

2015-05-14 12:04:06

Ben

Thanks for the suggestions but I couldn't get anything to work. The only way to copy was to open both sheets in the same instance of Excel. It is absolutely asinine that Excel can't perform the single most basic function of a modern computer, a function all previous versions could do. I have yet to see any new functionality in the last several versions - they just keep hiding common functions and taking away options.

2015-04-12 11:14:01

Sam Dertatevasion

I'm 90 and have been draged into the 21st century and I've just come from Quatro pro to Excel 2010. My current unsolved problem deals with coping a cell contents from sheet1 to a cell in sheet 2 such when I change the data in the sheet 1 cell, the sheet 2 cell will reflect the same change!

2015-03-13 13:09:07

Nikunj Bhatt

Just go for Copy & Paste Special Functionand paste as text.

2015-01-05 05:11:15

D.S.

I had the same problem, but after unclicking (deactivating) Smart View option for Excel, the issue disappeared. Will try to avoid keeping Smart View active all the time.

2015-01-01 15:27:06

Conrad Natac

I have this problem too. My work around is to move a copy of the worksheet from the other workbook to another. Then, once the worksheet is inside the workbook, I can do the my copying and pasting from the new worksheet to the rest of the worksheet.

2014-11-19 16:45:14

David

Gday all - my problem is that sometimes when I try to copy from one tab to the next the clipboard clears and I can't paste. I thought this was due to macros killing the paste but when I go to manage macros it shows there are none listed. What I do notice is when I go into one sheet and copy and then switch to the destination sheet, the formulas all kind of shimmer/ripple as if a macro is working. But again, the macro manager says there are none. Please help!!!

With dual screens you can of course stretch the instance across both and then minimise the sheets-no need for two instances. But, if you save a sheet while its sitting on the right screen and then try and open the file on a single monitor machine you'll be stuffed-windows tries to open it on a right screen that doesn't exist.

2014-10-30 11:24:51

Alan

as yo dudes said - copy and paste special uas unicode text works a treat...seemples

2014-07-28 20:13:28

Yo Dudes

If you are running two instances of Excel, (to have them on different screens). Copy and Paste Special as...wait for it.... UNICODE TEXT!!!!.... Thanks Mihail

2014-07-11 04:23:02

Lee

Absolutely bonkers that you can't copy and paste between two instances of excel, two Microsoft Products, using copy and paste from a Microsoft Windows OS.

Insanity

2014-05-26 19:51:50

Dave

Hi Allen

Sometimes it does not suit to have one instance with 2 workbooks open and you need the second instance to have the 2nd workbook on a second screen etc.I have found that often it is caused by a problem with "too many" text styles - once you remove the excess the problem goes away. Have you any other suggestions if you do need the second instance of a workbook?

2014-01-28 09:28:59

john

I'm hoping you can help me directly on my question - been pulling my hair out.

In EXCEL 2003 - copy/paste in and out of EXCEL works as it should.

We upgraded to 2007 EXCEL - when you copy the cell continues to blink - and you can paste (in this case) outside of EXCEL no problem.

The issue is if you copy from (outside the speadsheet) to paste back into it - the original cell is still blinking!?. When you paste, it paste(s) the original copied data!????

Please help!!!

2013-11-18 13:48:30

Jay

Thank you!! I've had this issue for months and none of the other suggestions I came across worked. I regularly work on two monitors with multiple instances of Excel.