Run Time Error 1004

[Paul]

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

 

[Joel]

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]

Hi,

It gets to row 60289 and pastes value half way across the row and then stops
and come up with the debug error message.

When i hover over the yellow highlighted code in the de bug window the value
says -4163 as a negative.

Hope this helps as I am stuck to know what the issue is??

Thanks

Paul

 

[Joel]

Which line of code is highlighted in yellow?????

Hi,

It gets to row 60289 and pastes value half way across the row and then stops
and come up with the debug error message.

When i hover over the yellow highlighted code in the de bug window the value
says -4163 as a negative.

Hope this helps as I am stuck to know what the issue is??

Thanks

Paul

 

[Paul]

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

Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues

Hi,

It's the bottom row in the above:-

Selection.PasteSpecial Paste:=xlPasteValues

Thanks

Paul

 

[Joel]

When you hover over the line you said you get -4163. You must be reading the
value of counter. Because counter is only getting incremented it must be
wrapping because it is getting to 4095 and incrementing by one and becoming
negative. This is an indication that "counter" is only being treated as an
integer. Declare Counter as Double (or long if double doesn't work).

 

[Paul]

I dimmed my counter variable as long but its still falling over in the same
place.

The counter gets to 285 out of 300 I am asking it to get to??

Thanks

Paul

 

[Joel]

You are now getting a different failure. It is a large area. I don't have
excel 2007 so I'm not sure what the limits are. I would try manually doing
the same operations on the worksheet and see if you get an error.

Highlight L60289:ANP60289

Then Copy and PasteSpecial with values and see if you get an error.