When you receive this error in Microsoft Excel, it would seem to be pretty self explanatory. Excel cannot insert the sheets into the destination workbook, because it contains fewer rows and columns than 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.
Excel 2003 and earlier spreadsheets (XLS file extension) has a max of 65,536 rows and 256 columns. With Excel 2007 and 2010 you now have the metro file format (XLSX, XLSM, etc.) which is capable of handling 1,048,576 rows and 16,384 columns of data. This error appears when you try to copy or move a metro file sheet to a non-metro (XLS) sheet. Basically you’re trying to fit a much larger page into a small book and still expect the sizes to be equal. Well, Excel doesn’t like that. In fact, it says “Uh, no” when you try to do this with a spreadsheet. That’s all well and good. But what about when you get this error running VBA code that used to work without a problem in Excel 2003? To answer this question you might want to take a look at your code. If you use a line of code with the .Move method this may well be your problem and here is why: if you are creating a separate workbook on the fly during your code operation, maybe for concatenating or manipulating data, and then later attempting to move a sheet from the newly created workbook into the legacy 2003 and earlier XLS file from which you are running the code, then you’re going to get this error. Why? When you run the code from Excel 2003 or earlier, any new workbooks created while running your code are also of the legacy format. But when you run the same macro in Excel 2007 or 2010 from the XLS file, you are creating a new XLSX metro workbook type and then telling Excel to stuff the larger page into the smaller one by issuing the move or copy command. Fine, so how do we work around this situation? There are a couple of ways. First, you can open the XLS file that contains the macro and click File > Save As before you run the macro. Save the file as an XLSM file type (metro file with a macro). Then when running the macro, you are working two equally–sized workbooks. You can then do another “Save As” to save the file back to the XLS legacy file format. The only drawback here is if you exceed the allowable rows and/or columns for legacy files.
Another workaround would be to use a copy and paste instead of a move or copy to copy the data that you need and paste that data into the XLS spreadsheet. Of course there are a couple of caviats with this solution. First, if you’re copying more than 65,536 rows of data or more than 256 columns of information then you won’t be able to paste it into the XLS file. You would get the same error message. Second, the code to copy and paste only the data you need is more involved than a one-line ‘Sheets.Move’ statement.
Hopefully, this will shed some light on why you might be encountering this error in VBA code. Let me know if you have any questions!
Here’s the problem. On a Windows 7 or Windows 2008 Server R2 machine, if you save your Excel 2010 documents into a My Documents (or subfolder like My Pictures) that has been re-mapped to a network location you may notice that it takes almost 40 seconds for the save to complete. The behavior is not consistent, however, as the latency does not occur every time you hit the save button. Fortunately, there is a fix for this problem. Apparently this was a known issue with Windows 7 and Windows 2008 R2, but the KB article to which this applies doesn't give any indication of the symptom, which is why I never found it. The hotfix contains an update for a Windows 7 networking driver which is causing the problem. The technical explanation: Excel 2010 makes an API call to Windows via a background thread which is waiting for the network driver to respond, which doesn't happen. The thread then times out and the save operation completes.
Solution:
1. Go to http://support.microsoft.com/kb/981711 – yes, that is the right KB article, even though the title talks about a stop error. Click the 'View and Request Hotfix Downloads' link at the top. Important: If you are downloading this hotfix for a computer other than the one you are viewing the article from then pay close attention to the link next to Step 1 on that page - the page uses an auto-detection script to determine if you are on x86, x64 or I64 systems. If you are wanting to apply this fix to an x86 machine and you are downloading from an x64 machine the hotfix you will receive by default is an x64 hotfix. If you click that link it will show all three hotfix types available. If you aren't sure which kind of machine you need it or, click the Start button and right click 'Computer' and left click on 'Properties'. Under 'System' the 'System type' will tell you what kind of operating system you are running.
2. Once you have the hotfix for the machine, just double click it. The exe file that you download will extract an MSU file to a directory you specify. After that's done, double click the extracted .MSU file to run it. A system reboot WILL be necessary in most cases. After the hotfix is applied you should no longer see the save problems. I applied the hotfix to the test machine I had set up and the problem did go away.