Run-time error 1004: Calculate method of Range class failed

[Gavona]

This routine has been working for weeks / years but suddenly is erroring with:
Calculate method of range class failed

With Range("Alldata")
    .Calculate
    .Cells.Copy
    .Cells.PasteSpecial (xlPasteValues)
End With

Security is set to low,
the worksheet is not protected
the range exists and is correctly defined
Watch on Range("Alldata").address shows expected range

Any ideas please?


Gavin

 

[SkipVought]

Try including the sheet object reference and, if necessary, the workbook object reference. Just a long shot.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[Gavona]

Thanks for the advice.
I'll try that later but the object in the watch window was the correct sheet, workbook etc, and that error message should only occur if the refrenced object is not a range.....

Update: I changed .calculate to calculate and that worked. Takes longer of course calculating the whole workbook but better than it not working.

I'll also try on my home PC in case it is related to some corporate update.

Gavin

 

[Gavona]

Solved it!

http://support.microsoft.com/kb/292476

CAUSE
This behavior occurs if the following conditions are true:
• You use the Range.Calculate method to calculate a range.
• On the Calculation tab of the Options dialog box:
• The Manual calculation option is selected.
• The Iteration check box is selected.
The Range.Calculate method does not function when both the Manual calculation option and the Iteration check box are selected.

I should have looked there / on google first!

Gavin

 

[SkipVought]

Thanks for posting the cause.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}