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.
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.
Program Successfully in Excel! John Walkenbach's name is synonymous with excellence in deciphering complex technical topics. With this comprehensive guide, "Mr. Spreadsheet" shows how to maximize your Excel experience using professional spreadsheet application development tips from his own personal bookshelf. Check out Excel 2013 Power Programming with VBA today!
Excel provides two really helpful shortcuts you can use to fill a range of cells, either horizontally or vertically. These ...
Discover MoreWant an easy way to insert a new row in a worksheet and copy everything from the row above? (You end up with two identical ...
Discover MoreIs your worksheet, imported from an external source, plagued by non-printing characters that show up like small boxes ...
Discover MoreFREE SERVICE: Get tips like this every week in ExcelTips, a free productivity newsletter. Enter your address and click "Subscribe."
2017-04-19 20:42:06
Steve B
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??
2015-12-02 06:23:24
Abhijeet K
Its a Virus issue.Scan Your System,delete the infected file & restart your PC.
2015-10-29 05:29:42
Maruf
Hii. It is very painful when I cut rows and past in the same sheet, then the sheet become 'not responding'. Do you have any solution?
2015-09-14 08:32:04
Ike
Excel 2010 :On destination excel sheet special paste "Match destination formating (M)
2015-09-11 10:12:03
Carlos Souza
Thank you by this tip!
this solve a problem with copying sheets from different excel files.
Carlos
from 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 Function
and 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.
Got a version of Excel that uses the menu interface (Excel 97, Excel 2000, Excel 2002, or Excel 2003)? This site is for you! If you use a later version of Excel, visit our ExcelTips site focusing on the ribbon interface.
FREE SERVICE: Get tips like this every week in ExcelTips, a free productivity newsletter. Enter your address and click "Subscribe."
Copyright © 2017 Sharon Parq Associates, Inc.
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: 
Comments