Excel VBA Copy and Paste - Paste Method of Worksheet Failed

[jhedges]

I have the following code, which works when first created; however, will fail it seems when the clipboard data is cleared. Could someone help figure out what is causing this run-time error 1004?

Sub AssignM1()
'
' AssignM1 Macro
    ActiveCell.FormulaR1C1 = _
        "=IF(ISERROR(VLOOKUP(PLDbase[[#This Row],[Personnel '#]],'Permanent Locker Dbase.xlsm'!MONTH1,3,FALSE)),"""",(VLOOKUP(PLDbase[[#This Row],[Personnel '#]],'Permanent Locker Dbase.xlsm'!MONTH1,3,FALSE)))"
    Range("M9").Select
    ActiveSheet.Paste
End Sub

 

[jhedges]

Update: - I was able to do the following:

Sub AssignM1()
'
' AssignM1 Macro
    ActiveWorkbook.Sheets("PL dbase").Activate
    Range("AF9").Select
    ActiveCell.Copy
    Range("M9").Select
    Selection.PasteSpecial Paste:=xlPasteFormulas, Operation:=xlNone, _
        SkipBlanks:=False, Transpose:=False
End Sub

This corrects the run-time error. Does anyone know if I can get away from selecting the formula as a cell and place it in the script instead?

 

[VoG]

Try

Sub AssignM1()
'
' AssignM1 Macro
    ActiveWorkbook.Sheets("PL dbase").Activate
    Range("AF9").Copy
    Range("M9").PasteSpecial Paste:=xlPasteFormulas
End Sub

 

[Andrew Poulsom]

Try:

Sub AssignM1()
'
' AssignM1 Macro
    With ActiveWorkbook.Sheets("PL dbase")
        .Range("AF9").Copy
        .Range("M9").PasteSpecial Paste:=xlPasteFormulas, Operation:=xlNone, _
            SkipBlanks:=False, Transpose:=False
    End With
End Sub

 

[jhedges]

Andrew,

Thanks for your help - looks like we came up with similar answers at about the same time.

This corrects the run-time error. Do you know if I can get away from selecting the formula as a cell and place it in the script instead?

Thanks
Jason

 

[Andrew Poulsom]

Sorry, I don't know what you mean.

 

[jhedges]

Andrew,

like in my first code example:

Sub AssignM1()
'
' AssignM1 Macro
    ActiveCell.FormulaR1C1 = _
        "=IF(ISERROR(VLOOKUP(PLDbase[[#This Row],[Personnel '#]],'Permanent Locker Dbase.xlsm'!MONTH1,3,FALSE)),"""",(VLOOKUP(PLDbase[[#This Row],[Personnel '#]],'Permanent Locker Dbase.xlsm'!MONTH1,3,FALSE)))"
    Range("M9").Select
    ActiveSheet.Paste
End Sub

I put the formula directly in the script to be pasted in. With the answers we came up with at the same time the formula I want to paste is in a cell I have hidden. I was trying to find a way to not have the formula in the worksheet in case it gets over written...

 

[Andrew Poulsom]

What is the ActiveCell when you run that code?