"Method 'PastSpecial' of object 'Range' failed"

[anon341]

Hello All,

I'm having errors with some of my VBA code, getting a "Method 'PastSpecial' of object 'Range' failed" for the below. Anyone able to assist?

Sub BuildMR()

'Turn off automatic formulas
Application.Calculation = xlManual

'Define parameters
Dim r As Integer
Dim lastcolumnP As Integer, lastcolumnH As Integer, lastrow As Integer

'Set the borders of the grid
lastcolumnP = Sheet41.Cells(2, 19).End(xlToRight).COLUMN
lastcolumnH = Sheet41.Cells(2, 140).End(xlToRight).COLUMN
lastrow = Sheet41.Cells(2, 4).End(xlDown).ROW

r = 3

'add percentages and hours
Do While r <= lastrow
    'Check percentage
    Sheet41.Cells(r, 17).Formula = "=rounddown(sum(RC[2]:RC[120]),0)"
    'percentages
    Sheet41.Cells(r, 19).Formula = "=IFERROR((1/(P" & r & "-O" & r & "))*(IF(OR(EOMONTH(P" & r & ",0)<$S$2,EOMONTH(O" & r & ",0)>$S$2),0,(N($S$2)-N(O" & r & "))-(IF(EOMONTH(P" & r & ",0)=$S$2,(N($S$2)-N(P" & r & ")),0)))),0)"
    Sheet41.Cells(r, 20).Value = "=IFERROR((1/($P" & r & "-$O" & r & "))*(IF(OR(EOMONTH($P" & r & ",0)<T$2,EOMONTH($O" & r & ",0)>T$2),0,(N(T$2)-N($O" & r & ")-(SUM($S" & r & ":S" & r & ")*($P" & r & "-$O" & r & ")))-(IF(EOMONTH($P" & r & ",0)=T$2,(N(T$2)-N($P" & r & ")),0)))),0)"
    Sheet41.Cells(r, 20).Copy
    Sheet41.Range(Cells(r, 21), Cells(r, lastcolumnP)).PasteSpecial
    'hours
    Sheet41.Cells(r, 140).Formula = "=$H" & r & "*S" & r
    Sheet41.Cells(r, 140).Copy
    Sheet41.Range(Cells(r, 140), Cells(r, lastcolumnH)).PasteSpecial
'Next row
r = r + 1
Loop

'Turn on automatic formulas
Application.Calculation = xlAutomatic

End Sub

Thanks!

 

[Fluff]

Hi & welcome to MrExcel.
You need to qualify all the ranges/cells with the sheet like

Sheet41.Range(Sheet41.Cells(r, 21), Sheet41.Cells(r, lastcolumnP)).PasteSpecial