1

I am having an issue whereby if there is nothing to paste I want to GoTo Err1: and if not I wan to continue with the paste.

This is the code I have but it always jumps to Err1: even if there is something to paste. 

Selection.Copy

On Error Resume Next
Sheet2.Range("A3").Paste
'~~~~> Want to skip to Err1: which will display a msgbox if nothing to paste
If Err Then GoTo Err1:
'~~~~> Want to continue here if there is something to paste
Range("BC3:BF3").Select
Range("BC3:BF3").Select
Application.CutCopyMode = False
Selection.AutoFill Destination:=Range("BC3:BF142")
Sheet3.Range("B8").Select
ActiveWorkbook.RefreshAll
Range("I7").Select
ActiveWorkbook.RefreshAll
Pᴇʜ
  • 56,719
  • 10
  • 49
  • 73
mhaverk
  • 57
  • 1
  • 7

1 Answers1

6

Your error handling should work like this:

Option Explicit

Sub MyProcedure()

    On Error GoTo PASTE_ERROR:

    Sheet2.Range("A3").Paste
    '~~~~> Want to skip to Err1: which will display a msgbox if nothing to paste

    On Error GoTo 0 'back to default error handling

    'other code

    Exit Sub 'exit here if no error
PASTE_ERROR:
    MsgBox "Paste Error"
End Sub

Additionally I recommend to read How to avoid using Select in Excel VBA.

Pᴇʜ
  • 56,719
  • 10
  • 49
  • 73
  • Thank you for the input however I still get it skipping to Paste Error every time even if there is data to paste. Any suggestions as to why? Could it be the code I am using to paste giving an error? – mhaverk Aug 24 '18 at 13:17
  • Been so long since I've used just `Paste` (usually use `.Copy Destination:=`) Syntax is `WorksheetObject.Paste Destination:=WorksheetRange` Helpfile: [MS Docs](https://learn.microsoft.com/en-us/office/vba/api/excel.worksheet.paste) so line pasting line **should be** `Sheet2.Paste Sheet2.Range("A3")`. – Darren Bartrup-Cook Aug 24 '18 at 13:29
  • I believe this should be `PasteSpecial` and not `Paste` in the given format. Otherwise, format should mimic @DarrenBartrup-Cook above comment – urdearboy Aug 24 '18 at 13:33
  • @udearboy Yes, either `Sheet2.Range("A3").PasteSpecial xlPasteAll` or `Sheet2.Paste Sheet2.Range("A3")` – Darren Bartrup-Cook Aug 24 '18 at 13:34
  • Actually.... `xlPasteAll` is the default for `PasteSpecial`? So just `Sheet2.Range("A3").PasteSpecial` would do as urdearboy said. – Darren Bartrup-Cook Aug 24 '18 at 13:36
  • I did however I think it was due to its placement in a bigger sub and the paste special issue. I have it working now. Thanks for all. the help to both PEH for the answer and darren-bartrup-cook for the followup. – mhaverk Aug 24 '18 at 13:53