none
Runtime-time Error 1004 Pastespecial method of Range Class field RRS feed

  • Question

  • Hi: We just upgraded to Windows 7 & Office 10 and one of my macro's now hang up with the run-time 1004 "Pastespecial method of Range class failed. I have looked at the links but don't see or understand those solutiions.

    Here is the code I currently have from the section where I get the error message.

    ' Application.CutCopyMode = False
       ' Sheets("PO").Copy
     
       ' ActiveWorkbook.SaveAs Filename:= _
        '    "g:\Raw Materials\Stock Board PO\& Range(L52).Value", FileFormat:= _
       '     xlNormal, Password:="", WriteResPassword:="", ReadOnlyRecommended:=False _
       '     , CreateBackup:=False
       ' ActiveWindow.Close
        ActiveWindow.SelectedSheets.PrintOut Copies:=2, Collate:=True
        Range("B52:g52").Select
        Selection.Copy
            Sheets("List").Select
            Application.Goto Reference:="R20000C1"
            Selection.End(xlUp).Select
            ActiveCell.Offset(1, 0).Select
            Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False
        Sheets("PO").Select

        Range("J5:M5,J3:K3,J1:K1").Select
        Range("J1").Activate
        Application.CutCopyMode = False
        Selection.ClearContents

    Regards

    Frank

    Wednesday, June 18, 2014 4:58 PM

All replies

  • I copied your code (or part of it and it seemed to work fine. However you have written the code moving the cursor always, which is very slow to run and not recommended try test2 below instead.

    Sub test()
        Range("B52:g52").Select
        Selection.Copy
        Sheets("List").Select
        Application.Goto Reference:="R20000C1"
        Selection.End(xlUp).Select
        ActiveCell.Offset(1, 0).Select
        Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False
    End Sub
    
    Sub Test2()
        Range("B52:g52").Copy
        With Worksheets("List")
            .Cells(.Cells.SpecialCells(xlCellTypeLastCell).Row + 1, 1).PasteSpecial _
                    Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False
        End With
    End Sub
    


    Rod Gill
    Author of the one and only Project VBA Book
    www.project-systems.co.nz

    Thursday, June 19, 2014 8:22 AM
  • Hi: Thank you  for the answer. It wouldn't run with the sub routine call & end so I put it in as this and it works.

     Range("B52:g52").Copy
       
    With Worksheets("List")
           
    .Cells(.Cells.SpecialCells(xlCellTypeLastCell).Row + 1, 1).PasteSpecial _
                    Paste
    :=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False
       
    End With

    Thank you very much.

    Frank

    • Proposed as answer by Rod Gill Friday, June 20, 2014 9:06 AM
    Thursday, June 19, 2014 5:51 PM