Page 1 of 2 1 2 LastLast
Results 1 to 10 of 11

Thread: Excel not allowing to move/copy a worksheet to another workbook

  1. #1

    Excel not allowing to move/copy a worksheet to another workbook



    Register for a FREE account, and/
    or Log in to avoid these ads!

    Excel 2010. I am trying to copy a worksheet from one workbook to another. Both are .XLXS files and I have been able to do this for quite some time with no problems, but recently when I try to copy a worksheet I get the message... 'Excel cannot insert the sheets into the destination workbook, because it contains fewer rows and columns that the source workbook. To move or copy the data to the destination workbook, you can select the data and then use Copy and Paste commands to insert it into the sheets of another workbook.'
    I did the copy and paste thing but lost all the formatting so I tried Paste Special and got the option to paste as Unicode or Text, not option to paste formating.
    I can copy a worksheet within the same workbook, and can copy a worksheet to a 'new workbook', but cannot copy a worksheet from one workbook to another.

  2. #2
    Administrator Ken Puls's Avatar
    Join Date
    Mar 2011
    Location
    Nanaimo, BC, Canada
    Posts
    2,209
    Articles
    57
    Blog Entries
    14
    Hi there,

    The workbook you are copying from is either an xlsx or xlsm file, and the workbook that you're trying to copy to is an xls file. The issue you're having is that, while the xlsx and xlsm files have over 1 million rows, the row limit in the old xls format was 65,536 rows.

    To solve this you need to either copy the data, as suggested by the message you got, or you need to save the workbooks so that they are either both xlsx/xlsm files OR xls files. Once you do that, then'll you'll be able to freely copy them.

    My suggestion would be to upgrade the target to the new file format, as that has the least risk of causing any issues... providing that everyone else who needs that workbook can open the newer file formats.

    Hope that helps,
    Ken Puls, FCPA, FCMA, MS MVP (Excel)

    Learn to Master Your Data at the Power Query Academy (the world's most comprehensive online Power Query training) or with my book M is for Data Monkey!

    Main Site: http://www.excelguru.ca -||- Blog: http://www.excelguru.ca/blog -||- Forums: http://www.excelguru.ca/forums
    Check out the Excelguru Facebook Fan Page -||- Follow Me on Twitter

    If you've been given VBA code (a macro) for your solution, but don't know where to put it, CLICK HERE.

  3. #3
    Ken, thank you for your suggestions, but they were of no help.
    I have gone back and verified that all files are .xlsx. The problem seems to stem from any Excel workbook I download from our company's server. Queries are run on our data in Great Plains and the results are then exported to Excel 2010. I then save the file to my local drive. Once I open that file, I can no longer copy and Paste Special on that workbook or any other excel workbook I have open. I cannot copy or move a worksheet either. If I close Excel and clear the cache or re-boot my machine, I can then copy and paste special or copy/move worksheets.
    If I only open Excel files that were created on my local drive, I do not have this problem. This problem did not exist until a month or so ago. I have used the copy and paste special and copy/move worksheets with files downloaded from our server previously with no problem. Possibly there is something askew with the way our server saves the file?

  4. #4
    Administrator Ken Puls's Avatar
    Join Date
    Mar 2011
    Location
    Nanaimo, BC, Canada
    Posts
    2,209
    Articles
    57
    Blog Entries
    14
    Strange,

    There's two things I can think of that can cause PasteSpecial issues:
    1)The message you gave above I have only ever seen happen when trying to copy from an xlsx/xlsm file into an xls file. This wouldn't kill PasteSpecial off, it just says that you've got too much data to put into the destination sheet.
    2) The other thing I've seen that affects PasteSpecial options is when you run with two instances of Excel.exe open. You can still copy from one workbook to another, but the pastespecial loses a lot of options giving you just HTML, Unicode Text and Text.

    I'm trying to think what else could possibly interfere here... if the great plains output is in an XLSX file, then it shouldn't be able to contain any macros at all... I'm going to have to give this some thought.
    Ken Puls, FCPA, FCMA, MS MVP (Excel)

    Learn to Master Your Data at the Power Query Academy (the world's most comprehensive online Power Query training) or with my book M is for Data Monkey!

    Main Site: http://www.excelguru.ca -||- Blog: http://www.excelguru.ca/blog -||- Forums: http://www.excelguru.ca/forums
    Check out the Excelguru Facebook Fan Page -||- Follow Me on Twitter

    If you've been given VBA code (a macro) for your solution, but don't know where to put it, CLICK HERE.

  5. #5
    Got lucky and came up with a way to "fool" the program. I copied the tabs I wanted to a "New Book," then copied from the new book to the target workbook. Don't know why that works.

    mcn

  6. #6
    Try this. It worked for me. Check that the workbooks are of same type. i.e. both are .xlxs workbooks. If not convert to same format. Close excel. Restart excel. Open both workbooks from inside the file dialogue box under Recent Documents. That is workbooks should be opened from same instance of excel opening. Select the worksheet to be copied. Right click. Click on move or copy tab. Click on Create a copy. Select the workbook to which worksheet is to be opened from To Book and choose location from "before sheet". That's it.

  7. #7
    Problem seems to be that a workbook originating under Excel2003 or before has a maximum rows of 65535, whereas Excel2010 or after workbooks have the 1m-odd limit if created under Excel2010. Solution when trying to copy a worksheet to an 'old' workbook (even if now xlsx) is to save the new worksheet as .xls, close it, and then re-open the new version (assuming you have fewer than 65535 rows, of course!) when the usual move/copy will work - no need to close down Excel.
    Haven't tried upgrading target workbook to avoid this problem in the future (worried about damaging it), but presume that using 'Save As' and using .xlsx format, then unloading and re-loading might just cure the overall problem. However, there may be a problem with NORMAL.XLT (I think) which is the default template and may determine the default limit, so you may need to re-build this too.

  8. #8
    Maybe u should copy not complete file but only select the required data and try to move to the table.

  9. #9
    Why my avatar is so angry?

  10. #10

    Excel tab moving resolved

    Thanks for the info below, it helped me a lot.

    Quote Originally Posted by Avinash gupta View Post
    Try this. It worked for me. Check that the workbooks are of same type. i.e. both are .xlxs workbooks. If not convert to same format. Close excel. Restart excel. Open both workbooks from inside the file dialogue box under Recent Documents. That is workbooks should be opened from same instance of excel opening. Select the worksheet to be copied. Right click. Click on move or copy tab. Click on Create a copy. Select the workbook to which worksheet is to be opened from To Book and choose location from "before sheet". That's it.

Page 1 of 2 1 2 LastLast

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •