Runtime error 1004 when using Cut and PasteSpecial

I have to cut the contents from active cell for a specific range in sheet 1 and paste it into the active cell of that range in sheet 2. Cutting the specific range of contents is working fine, but in the paste options I am getting runtime error 1004 as application defined or object defined error.

Here is the code I am using:

Sub sheet1_sheet2_copy_click()
  Sheets("sheet1").Activate
  ActiveCell.Resize(1, 26).Cut
  Sheets("sheet2").Activate
  ActiveCell.Resize(1, 26).PasteSpecial
End Sub

3 answers

  • answered 2017-06-17 19:33 Plaidpenguinhat

    Personally, I think using the active cell as a source and destination is rather unreliable, but if you change the paste line to activesheet.paste it should work fine.

  • answered 2017-06-17 19:33 SandPiper

    Your question is a little unclear, but you can try doing this:

    Sub sheet1_sheet2_copy_click()
    
    Sheets("sheet1").Cells(1, 26).Cut
    Sheets("sheet2").Cells(1, 26).PasteSpecial Paste:=xlPasteValues
    
    End Sub
    

    Better yet, you can just do this:

    Sub sheet1_sheet2_copy_click()
        Sheets("sheet2").Cells(1, 26).value = Sheets("sheet1").Cells(1, 26).Value
    End Sub
    

    In general, it is better to not use .Activate or .Select, but to instead explicitly define your references like I showed. Modify the Paste:= to whatever parameter you are trying to use for PasteSpecial.

    See https://msdn.microsoft.com/VBA/Excel-VBA/articles/range-pastespecial-method-excel for more information on this command.

  • answered 2017-06-17 19:33 Sergio Prats

    I have tested your code and it looks like PasteSpecial only works if you use Copy instead of Cut, so you should modify your code this way:

    Sheets("sheet1").Activate
    ActiveCell.Resize(1, 26).Copy
    Sheets("sheet2").Activate
    ActiveCell.Resize(1, 26).PasteSpecial
    

    If you need to delete the data from sheet1, then do it after the PasteSpecial.