Results 1 to 6 of 6

Thread: Adding Error with Copy & Paste

  1. #1
    New Member
    Join Date
    Dec 2018
    Posts
    22
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Adding Error with Copy & Paste

    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.

    Code:
      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

  2. #2
    Board Regular jmacleary's Avatar
    Join Date
    Oct 2015
    Location
    at a desk in the UK
    Posts
    629
    Post Thanks / Like
    Mentioned
    15 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Adding Error with Copy & Paste

    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:
    Code:
      Sheets("Tab 2").Select    Range("A1").Select
          ActiveSheet.PasteOn 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"
    If my answer has helped, please vote using the Thanks or Like buttons on the left.
    John

  3. #3
    New Member
    Join Date
    Dec 2018
    Posts
    22
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Adding Error with Copy & Paste

    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

    Code:
        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

  4. #4
    New Member
    Join Date
    Dec 2018
    Posts
    22
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Adding Error with Copy & Paste

    Quote Originally Posted by jmacleary View Post
    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:
    Code:
      Sheets("Tab 2").Select    Range("A1").Select
          ActiveSheet.PasteOn 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"
    See post above, thanks

  5. #5
    Board Regular jmacleary's Avatar
    Join Date
    Oct 2015
    Location
    at a desk in the UK
    Posts
    629
    Post Thanks / Like
    Mentioned
    15 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Adding Error with Copy & Paste

    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/...mpty-clipboard
    If my answer has helped, please vote using the Thanks or Like buttons on the left.
    John

  6. #6
    New Member
    Join Date
    Dec 2018
    Posts
    22
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Adding Error with Copy & Paste

    Quote Originally Posted by jmacleary View Post
    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/...mpty-clipboard
    Thanks for the pointer, will have a read and seeh how I get on! seems a bit over my head at the minute.

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •