Are you getting to Row 65536? Excel 2003 will only go to 65536. Can you
tell me which line is highlighted in excel when the failure occurs.

"Paul" wrote:

Hi,

I am trying to run the following code but when i do it it falls over after
after about 200 lines with the following error message:-

Run Time Error 1004 Pastespecial Method of range class failed:-

Can someone please look at the code to find out why it's falling over on
this message?????? Thanks

Private Sub CommandButton1_Click()

'Works out how many setups we have on the PPM sample and calculate there value

Dim Message As String
Dim Myquestion As String

Application.ScreenUpdating = False


'Alerts the user how long it could take to run the calculations

Myquestion = "Are you sure you want to run calculation for " _
& Range("b1").Value - 11 & " accounts, this could take " _
& Round((Range("b1").Value - 11) / 2.4 / 60, 2) _
& " Minutes"

Message = MsgBox(Myquestion, vbQuestion + vbYesNo, "SetupCalculations")

If Message = vbNo Then

MsgBox "NO Calculations Done!!", vbOKOnly, "SetupCalculations"

Else

counter = 11
mynum = Range("b1")

Do While counter <= mynum

'The below few lines copy the fields needed to work out the asset setup value



Range("B10:ann10").Select

Selection.Copy
Range("B" & counter).Select

Range("B" & counter & ":ANN" & counter).Select

Selection.PasteSpecial xlPasteAll

Application.CutCopyMode = False


'The below few lines work out the asset value for setup assets

Application.CutCopyMode = True

Range("L60014:ANP60014").Select

Selection.Copy
Range("L" & counter + 60004).Select

Range("L" & counter + 60004 & ":ANP" & counter + 60004).Select

Selection.PasteSpecial xlPasteAll

Application.CutCopyMode = False

'The below few line will paste special values into the calculated setups
assets after they have been calculated. Doing this will
'save on file size and make the calcualtions run quicker.

Application.CutCopyMode = True

Range("B" & counter & ":ANN" & counter).Select

Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues

Application.CutCopyMode = False

Application.CutCopyMode = True

Range("L" & counter + 60004 & ":ANP" & counter + 60004).Select

Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues

Application.CutCopyMode = False

'The below line stop the code from copy mode and put the cursor back to cell
B9

mynum = mynum
counter = counter + 1

Loop

Range("b9").Select

Application.ScreenUpdating = True

Range("r3").Value = WorksheetFunction.Sum(Range("ANP60014:ANP1048576") ) /
WorksheetFunction.Sum(Range("C60014:C1048576"))

MsgBox "Your calculations are complete", vbOKOnly, "SetupCalculations"

End If

End Sub