Why am I getting an Error Message

[mkw4949]

For some reason that following line of code is generating an error message "PasteSpecial method of range class failed" but I dont know why. I was working before. . . Any suggestions would be helpful.

Alloc.Range("X12:EL358").PasteSpecial xlValues

Sub Compress_Values()
Dim bCalcMth As Boolean
Dim wsStrtWS As Worksheet
Dim rStrtCell As Range
Dim rCFCell As Range
Dim rLseUpCell As Range
Dim CstSprdCell As Range

Set wsStrtWS = ActiveSheet
Set rStrtCell = ActiveCell

Application.ScreenUpdating = False

If Application.Calculation = xlCalculationAutomatic Then
Application.Calculation = xlCalculationManual
bCalcMth = True
Else
bCalcMth = False
End If

LeaseUp.Activate
Set rLseUpCell = ActiveCell
LeaseUp.Range("F10:EF500").Copy
LeaseUp.Range("F10:EF500").PasteSpecial xlValues
rLseUpCell.Activate

CashFlow.Activate
Set rCFCell = ActiveCell
CashFlow.Range("G12:DU459").Copy
CashFlow.Range("G12:DU459").PasteSpecial xlValues
rCFCell.Activate
the following line of code is generating an error message for some reason. It was not doing this before. . . Anyone know what. The trouble code is


Alloc.Activate
Set rCstSprdCell = ActiveCell
Alloc.Range("X12:EL358").Copy
>>>>>>>Alloc.Range("X12:EL358").PasteSpecial xlValues<<<<<<<<
rCstSprdCell.Activate

If bCalcMth = True Then
Application.Calculation = xlCalculationAutomatic
Else
End If

wsStrtWS.Activate
rStrtCell.Activate
Application.CutCopyMode = False
Data.Range("D14") = 0

Application.ScreenUpdating = True
End Sub

 

[VoG]

Instead of

Alloc.Range("X12:EL358").Copy
Alloc.Range("X12:EL358").PasteSpecial xlValues

try

With Alloc.Range("X12:EL358")
    .Value = .Value
End With

 

[mkw4949]

That did the trick. Thank! I have a silly question. how does the "With" statement work? You can probably tell from my code that I'm not much of a programer. I've learned by trial and error (more error really) and a couple of books. Thanks for your help.

 

[VoG]

Anything inside a With/End with construct that is preceded with a . refers to the object that the With refers to. Clear as mud? Try clicking in the word With in the code and press F1 to get the Help on it.

 

[mkw4949]

Thanks! Take care.