Excel 2007 move/copy sheet error

  • Thread starter Thread starter Terry B
  • Start date Start date
T

Terry B

This one error in Excel 2007 makes constantly makes me return to using Excel
2003:

"Excel cannot insert the sheet into the destination workbook, because it
contains fewer rows and columns than the source workbook."

WHAT!

This is absurd. I've been through Excel 3 until now and this has never
occurred previously. This limitation severely limits how I operate DAILY
with Excel. I realize some of the workbooks I'm using were originally
created with an older version of Excel than 2007 (say v2003 or even v5.x)
but it still makes no sense.

I've had to rewrite macros (that are using Excel 5 - 2003 based templates to
import data into) and calculate which sheet file is LARGER to import(move or
copy a sheet) to a SMALLER file! This makes no sense. Note that all
templates are NON 2007 Excel based and I'm running Excel 2007 in
compatibility mode and saving that way also.

Other than this work around I've had no luck on a solution.

Prior versions of Excel handled row changes using the move/copy sheet. Max
columns was consistent between prior until Excel 2007.

Excel 5 had a 16,384row / 256column limit,
Excel 97-2003 had a 65,536row / 256column limit,
Excel 2007 has 1,048,576row / 16,384column limit.

It seems as if Excel 2007 with its 16,384 column limit should EASILY import
'smaller' sheets into 'larger' ones, especially using older 256 column
limited sheets. Excel 2007 wants you to create a blank sheet and copy the
data from the other sheet. Problem is I have many defined ranges that I
don't want to re-define. It may be convenient for Excel 2007 but how can
this be making things easier for the end user?

Has anyone else been this upset by this?

Terry B

P.S. The ribbon menu was not for me either!
 
The message you're getting occurs when you try to move or copy an Excel 2007
worksheet into a workbook created with prior version of Excel which is open
in compatibility mode. You can move or copy prior version worksheets into an
Excel 2007 workbook with no problem. Excel 2007 completes the message
you're getting by suggesting you cut, copy and paste the data on the Excel
2007 worksheet into the prior version workbook.
 
After more research I've found that there's a little more to it.

I'm using Excel 2007 in [Compatibility Mode] and have it set to save all
documents Excel 97-2003 format.

When Excel 2007 'converts' a non Excel file such as a .txt / .csv / .dbf /
etc, it's using a 'new' XL2007 format spreadsheet (even running in
[Compatibility Mode]). If you save the file as an Excel 97-2003 document and
attempt a move/copy to an already open Excel 97-2003 document you will
experience the problem described earlier. The work around is to close the
newly saved document and re-open it. Now it will allow a move/copy sheet
operation.

After saving the file the header does not state [Compatibility Mode] in the
title until its re-opened.

1. (At Least) Excel 2007 should switch the newly saved file to
[Compatibility Mode] immediately after saving in the Excel97-2003 format.

2. (Better) Excel 2007 should assume that when in compatibility mode a new
spreadsheet in should open with Excel 97-2003 [Compatibility Mode]
parameters... unless you save it to the new 2007 format. At that point it
should REMOVE [Compatibility Mode] from the header and not require a close
and re-open.


Terry B
 
Excel enters compatiblity mode (as seen in the title bar) when it opens a
workbook saved in a prior release format. Compatibility mode is applied to
such a workbook. It is not a mode that Excel 2007 operates in. When you
create a workbook in Excel 2007 format and save it in a prior version format
does not necessarily imply you want to switch to the prior version format.
If so, close and open the newly saved workbook. Even if you have a workbook
open in compatibility mode, there is no reason for Excel to assume that a
new workbook should be in a prior version format. If you have a workbook
open in compatibility mode and another workbook open in native file mode
(Excel 2007) and you create a new workbook, should Excel create the workbook
in prior version format or native file format? Excel 2007 was designed to
work in native file format. Perhaps you should switch your files to Excel
2007 format to take advantage of the new features in Excel 2007. If you use
the new features and save in a previous file format, you'll lose the new
features because Excel cannot convert them to previous formats because prior
versions of Excel don't understand the new Excel 2007 features. Or perhaps
you should install Excel 2003 and work with that version.

Terry B said:
After more research I've found that there's a little more to it.

I'm using Excel 2007 in [Compatibility Mode] and have it set to save all
documents Excel 97-2003 format.

When Excel 2007 'converts' a non Excel file such as a .txt / .csv / .dbf
/ etc, it's using a 'new' XL2007 format spreadsheet (even running in
[Compatibility Mode]). If you save the file as an Excel 97-2003 document
and attempt a move/copy to an already open Excel 97-2003 document you will
experience the problem described earlier. The work around is to close the
newly saved document and re-open it. Now it will allow a move/copy sheet
operation.

After saving the file the header does not state [Compatibility Mode] in
the title until its re-opened.

1. (At Least) Excel 2007 should switch the newly saved file to
[Compatibility Mode] immediately after saving in the Excel97-2003 format.

2. (Better) Excel 2007 should assume that when in compatibility mode a new
spreadsheet in should open with Excel 97-2003 [Compatibility Mode]
parameters... unless you save it to the new 2007 format. At that point it
should REMOVE [Compatibility Mode] from the header and not require a close
and re-open.


Terry B


Tyro said:
The message you're getting occurs when you try to move or copy an Excel
2007 worksheet into a workbook created with prior version of Excel which
is open in compatibility mode. You can move or copy prior version
worksheets into an Excel 2007 workbook with no problem. Excel 2007
completes the message you're getting by suggesting you cut, copy and
paste the data on the Excel 2007 worksheet into the prior version
workbook.
Click to expand...
Click to expand...
 
FYI:

I appreciate your response Tyro and understand your suggestions.

Don't get me wrong, Excel 2007 has lots of 'cool' new features I prefer and
formatting styles for charts, etc.. The defined name range manger in Excel
2007 really helps. The ribbon still makes me work harder to locate commands
formerly listed in drop down menus (Everyone I know who has used prior
versions of Excel tells me the same thing). I've had to change from a single
tab for Excel in the task bar to multiple tabs (for each open document)
because I really hate the extra steps required by the ribbon to switch
between documents. At least I have a work around.

But, to share data properly between older versions I must work in
Excel97-2003 in my office. Our macros must run on all versions of Excel.
Upgrading all users to 2007 is not an option. Its the real world....not a
perfect XL 2007 world I work in. I have Excel 2007, 2003 and 5 on my PC and
use all of them! 97 has features I use also.
 
I personally find the ribbon far more intuitive, easier and faster to use
than the menus. Regarding switching documents, from the View tab on the
ribbon, right click on Switch Windows and click on Add to Quick Access
Toolbar.
..
 
I personally find the ribbon far more intuitive, easier and faster to use
than the menus. Regarding switching documents, from the View tab on the
ribbon, right click on Switch Windows and click on Add to Quick Access
Toolbar.
Click to expand...

Dude, I feel your pain on this.

I've previously developed a bunch of Macros that open .csv and .txt
type files and pull data into specialized Excel workbooks. I upgraded
to Excel 2007, everyone else in my office has Excel 2003/XP/2k.
Sucks.

I want to run in compatibility mode ALL OF THE TIME! ie. I don't care
about the extra rows/columns except in very specific circumstances. I
can't find any way to force .csv and .txt files to open in
compatibility mode.

Have you had any luck on this?

2007 sucks for experienced users. Period.
 
I personally find the ribbon far more intuitive, easier and faster to use
than the menus. Regarding switching documents, from the View tab on the
ribbon, right click on Switch Windows and click on Add to Quick Access
Toolbar.
Click to expand...

Dude, I feel your pain on this.

I've previously developed a bunch of Macros that open .csv and .txt
type files and pull data into specialized Excel workbooks. I upgraded
to Excel 2007, everyone else in my office has Excel 2003/XP/2k.
Sucks.

I want to run in compatibility mode ALL OF THE TIME! ie. I don't care
about the extra rows/columns except in very specific circumstances. I
can't find any way to force .csv and .txt files to open in
compatibility mode.

Have you had any luck on this?

2007 sucks for experienced users. Period.
 
Dude, I feel your pain on this.

I've previously developed a bunch of Macros that open .csv and .txt
type files and pull data into specialized Excel workbooks. I upgraded
to Excel 2007, everyone else in my office has Excel 2003/XP/2k.
Sucks.

I want to run in compatibility mode ALL OF THE TIME! ie. I don't care
about the extra rows/columns except in very specific circumstances. I
can't find any way to force .csv and .txt files to open in
compatibility mode.

Have you had any luck on this?

2007 sucks for experienced users. Period.- Hide quoted text -

- Show quoted text -
Click to expand...

Maybe the excel files are corrupt or something. You can try a popular
Excel file recovery tool called Advanced Excel Repair to repair your
Excel file. It is a powerful tool to repair corrupt or damaged Excel
files.

Detailed information about Advanced Excel Repair can be found at
http://www.datanumen.com/aer/

And you can also download a free demo version at http://www.datanumen.com/aer/aer.exe

Hope this will help.

Alan
 
Hi
The problem is one of the worksheets version is from an earlier version of Excel than 2007. (Probably a 2003 .xls). Load the original sheet into 2007 and save it as one of its versions. (xlsx or xlsm). Then it will work.
 
Back
Top