Loading
Excel.Tips.Net ExcelTips (Menu Interface)

Can't Copy Data between Workbooks

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.

Related Tips:

Save Time and Supercharge Excel! Automate virtually any routine task and save yourself hours, days, maybe even weeks. Then, learn how to make Excel do things you thought were simply impossible! Mastering advanced Excel macros has never been easier. Check out Excel 2010 VBA and Macros today!

 

Leave your own comment:

*Name:
Email:
  Notify me about new comments ONLY FOR THIS TIP
Notify me about new comments ANYWHERE ON THIS SITE
Hide my email address
*Text:
*What is 5+3 (To prevent automated submissions and spam.)
 
 
           Commenting Terms

Comments for this tip:

ronak    31 Aug 2016, 08:00
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..
Kulbir Singh    20 Jun 2016, 10:56
Thanks a lot

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

Thanks once again
Walt McKee    16 Feb 2016, 17:10
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
Lynn Foster    16 Dec 2015, 13:35
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.
Nancy    09 Dec 2015, 10:20
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??
Abhijeet K    02 Dec 2015, 06:23
Its a Virus issue.Scan Your System,delete the infected file & restart your PC.
Maruf    29 Oct 2015, 05:29
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?
Ike    14 Sep 2015, 08:32
Excel 2010 :On destination excel sheet special paste "Match destination formating (M)
Carlos Souza    11 Sep 2015, 10:12
Thank you by this tip!
this solve a problem with copying sheets from different excel files.
Carlos
from Brazil
David    03 Aug 2015, 11:20
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.
Ben    14 May 2015, 12:04
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.
Sam Dertatevasion    12 Apr 2015, 11:14
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!
Nikunj Bhatt    13 Mar 2015, 13:09
Just go for Copy & Paste Special Function
and paste as text.
 
D.S.    05 Jan 2015, 05:11
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.
Conrad Natac    01 Jan 2015, 15:27
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.
David    19 Nov 2014, 16:45
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.
Alan    30 Oct 2014, 11:24
as yo dudes said - copy and paste special uas unicode text works a treat...seemples
Yo Dudes    28 Jul 2014, 20:13
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
Lee    11 Jul 2014, 04:23
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
Dave     26 May 2014, 19:51
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?
john     28 Jan 2014, 09:28
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!!!
Jay    18 Nov 2013, 13:48
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.
 
 

Our Company

Sharon Parq Associates, Inc.

About Tips.Net

Contact Us

 

Advertise with Us

Our Privacy Policy

Our Sites

Tips.Net

Beauty and Style

Cars

Cleaning

Cooking

DriveTips (Google Drive)

ExcelTips (Excel 97–2003)

ExcelTips (Excel 2007–2016)

Gardening

Health

Home Improvement

Money and Finances

Organizing

Pests and Bugs

Pets and Animals

WindowsTips (Microsoft Windows)

WordTips (Word 97–2003)

WordTips (Word 2007–2016)

Our Products

Helpful E-books

Newsletter Archives

 

Excel Products

Word Products

Our Authors

Author Index

Write for Tips.Net

Copyright © 2016 Sharon Parq Associates, Inc.