So that everyone can use my Excel Charts and Dashboard Templates, I typically save my files in an Excel 97-2003 format. There are other good benefits of this file type, but we can talk about that on another day. Since most of my files are in the 2003 format, it sometimes causes me problems when I work between 2003 and Excel 2007, 2010 or now Excel 2013 format. Here is an error that I recently received.
I was I trying to move or copy a worksheet from one workbook to another workbook. You can do this by right clicking on the worksheet tab you want to move/copy:
Now my I forgot that the destination file was in this older format (Excel 97-2003) and I got the following error:
“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 the Copy and Paste commands to insert it into the sheets of another workbook.”
Hmmmm, that is not good. Sometimes you don’t want to copy and paste the data. For instance, check out this post on why you would want to copy or move the worksheet when you are working with Excel charts:
How-to Copy Charts and Change References to New Worksheet
Based on the error, it looks like I need to change the Excel workbook to the new Excel 2007 or greater format.
Likewise, the new Excel 2013 format has over 1 million rows in the spreadsheet
and the previous Excel 2003 format only has 65,000.
So you can’t copy or move a worksheet from the latest format to the older 97-2003 format.
Okay, so I don’t want to copy and paste the data, so it looks like I need to change the format of my destination file to the newer format. So I go to the destination file “Book1” (that has already been saved as a 97-2003 Excel format) and choose the File>Save As option. Then I change the Save As Type from Excel 97-2003 Workbook:
And change it to the current Excel format that is titled “Excel Workbook”
and press save.
I should be all set now, right? Wrong. After saving the file in the new type, I get the same error when I try and move the file:
And when I check the rows in the worksheet, it still only has 65,000 rows.
I then started checking all the settings in the workbook and application and started to pull out my hair, but, luckily there is a very simple fix.
It appears that all we needed to do was CLOSE the destination file after you have saved it as the Excel 2007 or greater format and then reopen it. Once you do, you will now see that it has over 1 million rows.
Even thought I am losing my hair, perhaps I can save you from pulling yours out when dealing with Excel. Sorry I have been away for a little bit, had a few life issues come up, but I should be posting regularly again over the next few weeks.
Here is a quick video demonstrating what I am talking about in this post:
Steve=True
