Runtime error 1004 - PasteSpecial method of range class failed

[danbates77]

Hi,

Please can someone help me?

I am trying to copy some data from one workbook to this one and if this code is triggered without anything being copied first, I get the following error

Runtime error 1004 - PasteSpecial method of range class failed

This is my code:

Sub Macro1()

Dim lrcd As Long

Application.EnableEvents = False
Application.ScreenUpdating = False
   
lrcd = Sheets("Auto_Changeover").Range("A" & Rows.Count).End(xlUp).Row

Sheets("Auto_Changeover").Cells(lrcd + 1, "A").Select

Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False
        
       Application.EnableEvents = True
    Application.ScreenUpdating = True
End Sub

and this is the offending line

Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False

Is there a way to stop the error happening if the code is triggered without any data being copied first?
Any help would be appreciated.

Thanks

Dan

 

[DanteAmor]

Try this:

Sub Macro1()

  Dim lrcd As Long
  
  Application.EnableEvents = False
  Application.ScreenUpdating = False
     
  lrcd = Sheets("Auto_Changeover").Range("A" & Rows.Count).End(xlUp).Row
  
  Sheets("Auto_Changeover").Cells(lrcd + 1, "A").Select
  
  On Error GoTo nothing_copy
  Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False
        
  Application.EnableEvents = True
  Application.ScreenUpdating = True
  Exit Sub
nothing_copy:
  MsgBox "nothing to copy"
End Sub

 

[danbates77]

Hi DanteAmor,

Perfect. Thank you so much, I really appreciate it.

Thanks

Dan