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.