Cannot select and then copy an entire worksheet, in Excel 2008

[1]

Version: 2008 Operating System: Mac OS X 10.6 (Snow Leopard) Processor: Intel Have been using excel 97 2004 with no problem for years. Now have new Mac and new excel spreadsheets which are 750 times bigger. Now, as earlier, I can still 'move and copy' a sheet over to a new workbook, but when I select the entire sheet (top left corner), then 'copy' the selection, excel freezes, and I am warned there is not enough memory.

I want to be able to create individual invoices from my own invoice template spreadsheet (which contains formulas referring to customer lists, prices, etc), 'move and copy' the (entire, preferred) sheet, and create a new spreadsheet for each new invoice created, in a separate invoice record workbook. I can get the sheet across from one workbook to another, no problem, but I then want to select the (entire) sheet I have just moved, paste special values in place of the - formula ridden - template, and then rename, so that I have that particular invoice as a values only record, with no formulas.
I don't need huge spreadsheets. Is it possible to reduce the size of 2008 spreadsheets, or find another way round this problem? I have tried saving as both .xls and as .xlsx but with no change in the problem.

 

[Bob Greenblatt]

Version: 2008 Operating System: Mac OS X 10.6 (Snow Leopard) Processor:
Intel Have been using excel 97 2004 with no problem for years. Now have
new Mac and new excel spreadsheets which are 750 times bigger. Now, as
earlier, I can still 'move and copy' a sheet over to a new workbook, but
when I select the entire sheet (top left corner), then 'copy' the
selection, excel freezes, and I am warned there is not enough memory.

I want to be able to create individual invoices from my own invoice
template spreadsheet (which contains formulas referring to customer
lists, prices, etc), 'move and copy' the (entire, preferred) sheet, and
create a new spreadsheet for each new invoice created, in a separate
invoice record workbook. I can get the sheet across from one workbook to
another, no problem, but I then want to select the (entire) sheet I have
just moved, paste special values in place of the - formula ridden -
template, and then rename, so that I have that particular invoice as a
values only record, with no formulas.
I don't need huge spreadsheets. Is it possible to reduce the size of
2008 spreadsheets, or find another way round this problem? I have tried
saving as both .xls and as .xlsx but with no change in the problem.

Two things may be happening. 1) Excel "thinks" the sheet is a lot bigger
than you do. You can find this out by selecting a cell in the template
sheet and then pressing control-end to select the last cell. If this is
much further to the right and down than where you think the last cell
ought to be, then that is why you may be running low on memory. To fix
this, select ALL columns to the right of the actual last data item and
got to Edit Delete. Likewise do this for ALL rows below the last data
item. make sure to use Edit-Delete, not pressing the delete key. Then
Immediately Close the workbook and clisk YES to save it. When it is
reopened the last cell should be where you think it should be.
2) Don't select the entire worksheet by clicking in the upper left
corner. Select from A1 to the last cell. Then copy and paste special
values and formats.

 

[1]

Bob thank you for taking the trouble. My data is set out within the area covered by A1 to DR68. How would you suggest selecting over a million rows below that, in order to delete them? - it looks like a lot of time would be taken up...

 

[Bob Greenblatt]

Bob thank you for taking the trouble. My data is set out within the area
covered by A1 to DR68. How would you suggest selecting over a million
rows below that, in order to delete them? - it looks like a lot of time
would be taken up...

You don't need to delete them unless the last cell is below row 68. If
it is then use the control down arrow to go to the bottom of the sheet,
then shift up arrow to select the cells above. then shift select the
entire rows, then delete them.

 

[CyberTaz]

PMFJI, and I don't mean to insult in any way, but just wanted t point out
that no matter what you do the additional rows (and columns) will not be
*removed* from the sheet. The number of cells is fixed, so 'deleting' rows
or columns simply replaces them with empty ones.

If your actual goal is to 'not see' the columns & rows you don't intend to
use follow Bob G's suggestion to select them but then use Format> Row> Hide
or Format> Column> Hide so the only visible cells will be the ones you need.

HTH |:>)
Bob Jones
[MVP] Office:Mac

 

[Bob Greenblatt]

PMFJI, and I don't mean to insult in any way, but just wanted t point out
that no matter what you do the additional rows (and columns) will not be
*removed* from the sheet. The number of cells is fixed, so 'deleting' rows
or columns simply replaces them with empty ones.

If your actual goal is to 'not see' the columns& rows you don't intend to
use follow Bob G's suggestion to select them but then use Format> Row> Hide
or Format> Column> Hide so the only visible cells will be the ones you need.

HTH |:>)
Bob Jones
[MVP] Office:Mac

Hi bob,
No, my suggestion was to remove any referenced but unused cells.
sometimes sheets grow unusually large after a lot of insertions and
deletions. The poster said he was running out of memory after selecting
all cells in a sheet. this may happen if there are, indeed, a lot of
referenced but unused cells.

 

[CyberTaz]

Hey Bob;

Hi bob,
No, my suggestion was to remove any referenced but unused cells.
sometimes sheets grow unusually large after a lot of insertions and
deletions. The poster said he was running out of memory after selecting
all cells in a sheet. this may happen if there are, indeed, a lot of
referenced but unused cells.

I fully understand & didn't mean to suggest otherwise I just run across
a lot of people who get the wrong impression & think that by using "Delete"
Rows/Columns that they're actually making the dimensions of the sheet
smaller. Likewise I've had people frustrated by using "Insert" Rows because
they still wound up with 65,536 (O2003/2004) no matter how many rows they
inserted or couldn't come up with more than 256 Columns.

Perhaps I read into the OP's request but I got the feeling that he not only
wanted to clear the content of the peripheral cells but also wanted to make
them go away altogether.

Regards |:>)
Bob Jones
[MVP] Office:Mac

 

[1]

If I may cut to the chase, (and I have understood both your points, and see them as valid within the limitations of Excel generally) I am concerned that I have had a stable working system in excel 97-2004 upon which I have based years of work, and upon which I depend for my income, and yet I cannot possibly be finessing worksheets like this in Excel 2008 and hope for a robust system in the future, upon which to base my business.

Therefore, may I ask if you know whether - given the Snow Leopard operating system with which I have now to work - I can revert to using Excel 97 - 2004, and thus avoid the difficulties inherent in Excel 2008, which appears to have been extended in a direction irrelavent to my needs?

 

[John McGhie]

Mmmm... That's not what they're saying...

You're getting a memory overflow, and Bob G thinks it's because the "Used
Area" of your spreadsheet has blown out beyond all comprehension.

What he's suggesting is a very quick manoeuvre, just a couple of clicks, to
trim that specific sheet back to wherever the actual data ends (row 68).

I would simply click in A69, then hit Command + Shift + End. That will
select from there all the way to wherever Excel thinks the bottom is. Then
simply "Delete".

You could indeed re-install Excel 2004. But if you did that, I suspect it
would simply refuse to load this spreadsheet, because E2004 does not support
the "big grid" that E2008 does.

Now, *I* believe the issue here is that your workbook has become corrupt.
*I* would suggest that you create a new workbook, carefully select only the
valid data from the old workbook and copy it into a new workbook.

Save it as a new file name in the new format (.xlsx).

Excel files do corrupt from time to time, and I suspect yours has.
Switching back and forth between file formats and versions of Excel is one
(or many...) possible causes of spreadsheet corruption. I recommend going
up to the new file format, which is much more rugged than the old one, and
staying there.

Sadly, there is a possibility that all three of us are off on the wrong
track with this issue. If you try one or other of the suggested methods,
we'll get more information, and be in a better position to guess what the
real problem is.

Cheers

If I may cut to the chase, (and I have understood both your points, and see
them as valid within the limitations of Excel generally) I am concerned that I
have had a stable working system in excel 97-2004 upon which I have based
years of work, and upon which I depend for my income, and yet I cannot
possibly be finessing worksheets like this in Excel 2008 and hope for a robust
system in the future, upon which to base my business.

Therefore, may I ask if you know whether - given the Snow Leopard operating
system with which I have now to work - I can revert to using Excel 97 - 2004,
and thus avoid the difficulties inherent in Excel 2008, which appears to have
been extended in a direction irrelavent to my needs?

--

The email below is my business email -- Please do not email me about forum
matters unless I ask you to; or unless you intend to pay!

John McGhie, Microsoft MVP (Word, Mac Word), Consultant Technical Writer,
McGhie Information Engineering Pty Ltd
Sydney, Australia. | Ph: +61 (0)4 1209 1410 | mailto:[email protected]