“Excel cannot insert the sheets into t

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!