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

[skbrla]

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.

[Ken Puls]

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,

[skbrla]

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?

[Ken Puls]

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.

[mcn]

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

[Avinash gupta]

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.

[pdjsingleton]

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.

[Lilia]

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

[Lilia]

Why my avatar is so angry?

[patrick_brosnan]

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

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.