Adding Error with Copy & Paste

[dan8825]

Hello I have the below code, which should show an error msg box and close the two workbooks the macro has already opened when there is nothing to paste.

This is working to well! as in it runs even when there is copied text.

  Sheets("Tab 2").Select    Range("A1").Select
      ActiveSheet.PasteOn Error GoTo ErrorHandler
ErrorHandler:
    Mainbook.Close Savechanges:=False
    wb1.Close Savechanges:=False
    MsgBox "Nothing to paste!": Err.Clear
    Exit Sub
    Range("A1:F500").Select
    Selection.Style = "Normal"

I need code to run an error message,end sub, close the workbooks it opened when there is noting to paste, otherwise run as normal.

Thanks

 

[jmacleary]

If this is your code as typed (i.e. you haven't chopped out any lines from the middle) then your problem is because you have the error code directly after the paste statement, so it will always be run. Insert an Exit Sub (shown in red) so it will be:

  Sheets("Tab 2").Select    Range("A1").Select
      ActiveSheet.PasteOn Error GoTo ErrorHandler
[COLOR=#ff0000]    Exit Sub[/COLOR]
ErrorHandler:
    Mainbook.Close Savechanges:=False
    wb1.Close Savechanges:=False
    MsgBox "Nothing to paste!": Err.Clear
    Exit Sub
    Range("A1:F500").Select
    Selection.Style = "Normal"

 

[dan8825]

Hi This is now the full code, the only bit proceeding this is opening the workbooks. This is not working it is pasting 'Activesheet.paste' into a workbook.

The copy would be done manually before hand, as this is in an external programme not another workbook

    Sheets("Tab 2").Select    Range("A1").Select
   ActiveSheet.Paste
   On Error GoTo ErrorHandler
    Exit Sub


ErrorHandler:
    Mainbook.Close Savechanges:=False
    wb1.Close Savechanges:=False
    MsgBox "Nothing to paste!": Err.Clear
    Exit Sub
    Range("A1:F500").Select
    Selection.Style = "Normal"

Thanks

 

[dan8825]

If this is your code as typed (i.e. you haven't chopped out any lines from the middle) then your problem is because you have the error code directly after the paste statement, so it will always be run. Insert an Exit Sub (shown in red) so it will be:

  Sheets("Tab 2").Select    Range("A1").Select
      ActiveSheet.PasteOn Error GoTo ErrorHandler
[COLOR=#ff0000]    Exit Sub[/COLOR]
ErrorHandler:
    Mainbook.Close Savechanges:=False
    wb1.Close Savechanges:=False
    MsgBox "Nothing to paste!": Err.Clear
    Exit Sub
    Range("A1:F500").Select
    Selection.Style = "Normal"

See post above, thanks

 

[jmacleary]

Hi there. As the copy is done outside, you can't just use the paste function. Have a look at this reference to see how to read the contents of the clipboard - it also shows you how to handle empty clipboard as well.
https://stackoverflow.com/questions...-using-gettext-avoid-error-on-empty-clipboard

 

[dan8825]

Hi there. As the copy is done outside, you can't just use the paste function. Have a look at this reference to see how to read the contents of the clipboard - it also shows you how to handle empty clipboard as well.
https://stackoverflow.com/questions...-using-gettext-avoid-error-on-empty-clipboard

Thanks for the pointer, will have a read and seeh how I get on! seems a bit over my head at the minute.