locked
Microsoft Excel cannot paste the data RRS feed

  • 質問

  • Hi,

    I have a user that is working on a Vista Terminal Server with Excel 2010 on it.  The user will copy a large amount of data in Excel 2010 and try to paste it into Excel 2010 on his local machine being Win 7 and Excel 2010.  Sometimes it works, for example if the terminal server has just been rebooted, but then most of the time Excel locks up and throws this error "Microsoft Excel cannot paste the data".  Has anyone ever had this problem and if so, have an answer for the problem?  Anyone have any suggestions?  Could it possibly be the clipboard?

    Any help is greatly appreciated.

    Thank You!

    2011年2月2日 16:42

すべての返信

  • Hi,

    Does your data have merged cells? If yes, that could be your problem. You cannot paste data from a merged cell range into a non-merged cells range. Both ranges have to be the same if a merged range
    is involved.

    Hope that helps.

    2011年2月3日 9:08
  • Hi, thank you for your reply.  The data does not contain merged cells.

    Thanks!

    2011年2月3日 15:24
  • Mine "didn't" either, but by selecting the data and choosing "unmerge cells" I was then able to copy.
    • 回答の候補に設定 BillSinc 2011年3月5日 17:50
    2011年3月2日 16:54
  • I tried choosing "unmerge cells" but was still unable to paste into a new document.  Any other suggestions?

    2011年8月16日 19:52
  • I couldn't get it to work either.  Worked on some spreadsheets, but not others.  Could not find any pattern.  So I copied cells to the other spreadsheet and chose the Paste Link icon (one with the chain symbol).  Then where the newly pasted cells were, I did another copy and paste choosing the Paste Values icon (123 symbol).  Not the best fix, but worked for now.
    • 回答の候補に設定 LHarbin 2011年10月11日 22:54
    2011年10月11日 22:53
  • I also have the same problems.  I tried LHarbin's way and it works! :) Thanks a lot!
    2011年10月12日 0:09
  • I have just recently started to having this problem, it began October 21.  I have no merged cells but tried the unmerging anyway - that was not helpful in this situation.  But LHarbin's idea to copy and paste link was.  Thank you. 
    2011年10月24日 17:26
  • yep that chain symbol  link and 123 repaste works, the problem I believe is once the file is excel file is corrupted you need to do this, this happens to me from time to time and is not a continious problem
    2011年11月10日 11:02
  • Someone in our office started having the same problem a few weeks ago as well

    It seems to depend on the spreadsheet.

    We'll try the 'unmerge'.

    2011年11月10日 20:39
  • I had the same problem on my machine.  I discovered that it was being caused by a program, Macro Express, that was running in the background.  Once I disabled that program, the problem went away.  Try looking to see what you have running in the background and disable them one at a time.
    2012年1月3日 14:54
  • Paste Link icon (one with the chain symbol) works - thanks. But amending values (dates,currency etc) no joy. Cannot even change font size or colour of one cell. Message: "Too many different cell formats."  Tried paste special.  Don't know how to check for programs running in the background or where to find Macro Express. Please advise.
    • 回答の候補に設定 Owen Yeager 2013年9月3日 22:30
    2012年1月3日 19:32
  • Hi LHarbin!

    Can You copy and paste cells with formulas this way?

    Renatoabc


    • 編集済み renatoabc 2012年1月5日 10:59
    2012年1月5日 10:59
  • I had the same problem.

    Save the file, close Excel, and then open the file. I was able to copy/paste without errors.

    2012年1月11日 19:20
  • definitelynojoy--

    The "Too many different cell formats" can lead to a very unstable workbook. Try consolidating cell formats by using styles. Google the too many different cell formats, that's an ugly one in terms of making a workbook unstable.

    2012年3月8日 16:18
  • Maybe this will help,

    I had way too many TABs (Worksheets). After deleting few I was able to paste without a problem.

    2012年3月8日 20:40
  • 2 files I am working on are located on different servers. 

    After opening the second file into the same instance by going to File>Open seems to resolve the issue.

    Disregard the TABs previously mentioned.

    Thanks,

    Ravi

    2012年3月12日 18:23
  • http://support.microsoft.com/kb/213904 This should do it I gonna give it a shot.

    2012年3月13日 19:39
  • i closed both files, and reopened, then it worked.
    2012年4月10日 21:42
  • I was having the same "cannot paste" problem. I was combining large amounts data from different sheets into one sheet. When I tried to copy 59000 rows into the sheet I started to get the error. I discovered that I was copying into an XLS excel sheet in compatibility mode. The problem is that using an xls sheet means that you have 65536 row maximum. I was trying to paste more rows into the sheet than is allowed. What I did was make sure to create a new worksheet(xlsx) and I copied in all the data I wanted with no problem. XLSX has up to a 1 million rows so you should be ok.
    • 回答の候補に設定 BadMadRad 2012年5月9日 18:30
    • 編集済み BadMadRad 2012年5月9日 18:30
    2012年5月9日 18:29
  • When experiencing this issue, I found that by right-clicking to paste, I was given to option to have the copied data match the formatting of the destination sheet (see picture.) I had tried copying and pasting values, etc., but that did not work for me. This seems like the easiest solution, unless you are running into data limits as mentioned (xls versus xlsx.)

    • 回答の候補に設定 jlongjr 2013年1月8日 19:53
    2012年9月13日 17:05
  • I had a similar "Excel cannot paste the data" issue, but mine was with copying cells from one workbook to another on the same machine.  This was between two instances of Excel. It turns out pasting from one to the other copied all the Normal cell styles from one workbook to the other (I am assuming if the "Normal" cell style from the source workbook doesn't match the destination workbook's Excel creates a copy on the destination one).  As you copy back and forth the destination and source workbooks' style list grows exponentially.  By the end of it, there were thousands of "Normal 1", "Normal 1 2" etc. styles that needed to be deleted else they'd be copied from one to the other.  Since Excel maxes out at ~65,000 styles, once you get over 32,500 styles copying would ruin you if it tried to move all those styles. 

    I ended up running a macro that deleted all but the built-in styles and that resolved my problem. Macro is below.

    Sub StyleKill()
         Dim styT As Style
         Dim intRet As Integer
         On Error Resume Next
         For Each styT In ActiveWorkbook.Styles
             If Not styT.BuiltIn Then
                 If styT.Name <> "1" Then styT.Delete
             End If
         Next styT
     End Sub


    • 回答の候補に設定 Jfo316 2012年9月27日 15:27
    • 編集済み Jfo316 2012年9月27日 15:31
    2012年9月27日 15:27
  • I tried saving and closing both files, re-opening, and was able to cut and paste.

    Thank you!

    2013年5月20日 18:07
  • I went to this link downloaded the fix tool and ran it on the file I was having the same issues as cannot paste and to many stles this removed these errors which was a build up over time of using the same sheet over and over monthly. once cleaned everything is working as it should.

    sergeig888.wordpress.com/2011/03/21/net4-0-version-of-the-xlstylestool-is-now-available

    2013年9月3日 22:31
  • Hello,

    I got it to work this way but it seems to have removed the format of the copy/pasted items into the spreadsheet. All items in the sheet should have the same format as they are a monthly run report. Any quick idea to correct the small data pasted with the format issue?

    Thanks

    2013年9月12日 14:06
  • I was also facing the same problem.

    Your suggestion helped me to conclude.. Thanks!

    Only thing is I simply copied all the data from destination file (contained various styles) to a new file, ensure unnecessary styles not copied and it resolved the issue.

    It worked since the file did not contain any link or macro, otherwise the VBA code is the best solution. 

    2013年10月4日 11:56
  • Hi,

    I had a similar problem. Although I am not an expert at excel and this solution is probably basic, have you tried opening the source excel spreadsheet and the destination excel spreadsheet in the same window (namely open the source spreadsheet and then use the open function to open the destination spreadsheet). It works every time for me. Hope this helps.

    2014年1月13日 21:15
  • The copy-paste link method works...thanks!

    2014年1月14日 14:47
  • This bug definitely needs to be raised back to Microsoft (hopefully someone here with a Microsoft Connect membership can do it?)

    I get this error occasionally when pasting into a spreadsheet, then can't paste that same data into any spreadsheet during the same Excel 'session', whether it's an existing or a new spreadsheet.

    However, if I close Excel and paste into a blank spreadsheet (without any modification to the clipboard between the closing of excel and re-starting of a new session), the paste works fine. From that new blank spreadsheet I then copy/paste to the final destination spreadsheet without any issues.

    It seems as though one of the spreadsheets that I open during the 'faulty' session, causes the paste function to fail. I am pasting a data range spanning >150,000 rows and nearly 40 columns.

    2014年1月16日 0:34
  • This is what worked for me, as well, copying between servers. I do this every day copying into the same spreadsheet, and this doesn't happen often, but when it does, it's extremely frustrating.

    So thanks, Ravi.

    2014年2月6日 1:50
  • I had to create an acct here just to reply in hopes I could help others with this error as it was driving me nuts.

    There is one solution I have not seen suggested, that I determined I could reproduce 100% of the time for the messagebox error of "Microsoft Excel cannot paste the data" either between workbooks, worksheets, or even within the same worksheet.

    I wonder how many of these folks have clipboard managers/enhancers running?  Even though there is no legitimate reason a simple paste even of unformatted cells of only values would still result in the above error, but in my case having CLIPJUMP running and simply maintaining clipboard history of multiple entries, a simple single data item paste from Excel, to Excel, results in this Excel error.

    My clipboard manager <tries> to grab formatting, as do many others, which is why I believe some posted workarounds involving pasting into another app and back have helped.

    So, even though they may not ever conflict with anything.....disable <anything> that touches the clipboard in <any> way, and from what I can tell that will solve many occurrences of this error since different clipboard managers/enhancers have become more and more common.

    Simply shutting down my clipboard manager for the time I use Excel has 100% eliminated this error.

    2015年2月1日 17:27
  • This method still works!!

    Thanks!!!

    2015年7月20日 16:36
  • Crazy as it is, this solution worked for me as well.   LHarbin, Thank you for posting it.
    2016年2月26日 23:29
  • Hi,

    I have a user that is working on a Vista Terminal Server with Excel 2010 on it.  The user will copy a large amount of data in Excel 2010 and try to paste it into Excel 2010 on his local machine being Win 7 and Excel 2010.  Sometimes it works, for example if the terminal server has just been rebooted, but then most of the time Excel locks up and throws this error "Microsoft Excel cannot paste the data".  Has anyone ever had this problem and if so, have an answer for the problem?  Anyone have any suggestions?  Could it possibly be the clipboard?

    Any help is greatly appreciated.

    Thank You!

    Open the file and press crt+6
    2016年6月21日 18:22
  • You can also copy and paste and paste special then select unicode and that will also let you copy and paste
    2016年6月22日 17:34
  • In my case, it is due to the data volume hitting the row number limit 1,048,576 rows.
    2016年8月15日 19:28
  • nothing but your idea works for me!!!!!thank you so much!!!
    2017年6月22日 20:42
  • Hi,

    Thank you, It was really very helpful. I pasted same code mentioned by you and after running it problem disappeared as it kills the all styles and even the file size reduced from 400KB to 80 KB. God bless you.

    2017年8月2日 3:58
  • This is a bedeviling problem!  I am trying to paste data from a .pdf file so I can eyeball the data and manually enter it into a spreadsheet.  It has been working all along and then this "cannot paste the data" showed up. 

    It's only on this spreadsheet that the error occurs, and it occurs on every sheet in the workbook.  I tried deleting all the other worksheets in the workbook in hopes I could port the data over, but even when it is trimmed down to one worksheet, it still fails to paste.  There are only 700 entry lines in the worksheet.

    I am pasting image data copied from Acrobat (or trying to).

    I had to set up a dummy sheet and enter the data there and then copy it and paste into the sheet with the processing formulas.

    I turned off the "Ignore other applications..." in file settings/advanced menu (as recommended in another forum) and that did not work.

    Forgive my ignorance, but how does one "turn off the clipboard manager"? I didn't see it in the Task Manager list of background activity.

    Please advise!

    2018年3月19日 15:55
  • Thank you so much for this!  I have spent many hours trying to resolve the problem that I was having with a file that I use daily.  I ran this macro once, it took a long time so there must have been a ton of styles in there... and boom, I'm back in business! 
    2018年4月9日 20:51
  • This worked, thank you! You also have to open it in the same window to get it to keep working. I have dual monitors and would open two separate excel sessions so i could see on both and then it kept reverting. 
    2018年10月15日 19:43
  • This one worked brilliantly! Thanks so much!
    2019年2月7日 17:48