Excel crash on pasting as values

[Anand Nichkaode]

Hi,

I have a Excel 97-2003 workbook. And on one of the sheets there is a Table
inserted from Data->Table which does some interest calculation.

When this workbook is opened in Excel 2007 following code is run Excel
crashes.

Whereas in Excel 97 I get "Method PasteSpecial of object Range failed" error
for the first time. If I stop and re-run the code formulas for the table are
removed.

Code:
Cells.Select

Selection.Copy

Selection.PasteSpecial xlValues

Cells(1, 1).Select

Does anybody have any idea about this.

Pls. let me know if you want to have a look at the workbook.

Thanks in advance.

-Anand

 

[Vlado Sveda]

Excel 97-2003 has 64k rows by 256 columns while Excel 2007 has 1M rows by 16k
columns.
Cells.Select selects entire worksheet (which is different in 97-2003 format
and 2007 format) so my suspicion is about that.

Try to use different selection method

Vlado

 

[Anand Nichkaode]

Thx for a quick reply.

My problem is that I don't know where in the sheet user has put any formula.
So I select everything and try to paste as values.

Also why does it throws error first time and works perfectly next time in
Excel 97.

One more observation, in Excel 97 although it works perfectly second time
Excel crashes when tried to close with Out of Memory error.

-Thx
Anand

 

[Patrick Molloy]

if you've copy/pasted such a lot of data, you're gobbling memory. This isn't
efficiently released by excel - and is only released when you close the app.

Instead, try exporting the sheet as a CSV file and import that into
2007...after all, its the data, not the formulae you need

 

[Anand Nichkaode]

Hi,

As I have said earlier my workbook is a 97-2003 format so it would have only
64K rows.
And when I don't have this table on the sheet it works fine, but crashes
when there is this table.

Also the table is made up of 10R X 2C. And 90% cells on the sheet are empty.

Do you still think that memory management is an issue here?

-Thx
Anand

 

[Anand Nichkaode]

I changed my code to following but still Excel crashes.

Range("A1:L9").Select
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=False
Range("D4").Select

The table is located at 1,1 cell on the sheet.

This code is recorded with "Record Macro" option.

-Thx
Anand