Results 1 to 3 of 3

Thread: MS Excel Paste Data / Clipboard relative error?

  1. #1

    Thread Starter
    New Member
    Join Date
    Feb 2013
    Posts
    2

    MS Excel Paste Data / Clipboard relative error?

    I've written a macro which copies multiple cells and ranges from sheets(1) to multiple sheets. I must use both "ActiveSheet.Paste" and "ActiveSheet.Paste Link:=True". The ranges are large and there are more than 1000 copy/paste operations per sheet, so running the macro takes time, obviously.

    The code works great when Excel is the active application during the macro runs.

    But if I'm using other applications during Excel runs the macro, it usually crashes with error "Microsoft Excel Cannot Paste Data" or "Paste method of Worksheet class failed". I'm already using

    Code:
        Application.ScreenUpdating = False
        Application.EnableEvents = False
        Application.IgnoreRemoteRequests = True
    at the beginning of the code but they do not make much difference.

    I guess this has something to do with the clipboard. I was hoping to solve these errors and speed up the code by bypassing the clipboard, but since I'm using both "paste all" and "paste links" and the ranges are large and complex I don't know how to bypass the clipboard completely.

    Any help would be highly appreciated, thanks!

  2. #2
    PowerPoster
    Join Date
    Oct 2008
    Location
    Midwest Region, United States
    Posts
    3,574

    Re: MS Excel Paste Data / Clipboard relative error?

    You should be able to fix this by qualifying the copy sheet and the paste sheet (and the workbook(s) as well), something like this:

    Code:
    Sub pasteLink()
        Dim copyWB As Workbook
        Dim pasteWB As Workbook
        Dim copySheet As Worksheet
        Dim pasteSheet As Worksheet
        
        Set copyWB = Workbooks("copy1.xlsx")
        Set pasteWB = Workbooks("paste1.xlsx")
        Set copySheet = copyWB.Worksheets("sheet1")
        Set pasteSheet = pasteWB.Worksheets("sheet1")
        
        copySheet.Range("a1").Copy
        pasteSheet.Range("a1").PasteSpecial 'don't need to activate sheet in this case
        
        copySheet.Range("a2").Copy
        pasteSheet.Activate     'need to activate
        pasteSheet.Range("a2").Select   'select destination cell
        ActiveSheet.Paste link:=True
    End Sub

  3. #3

    Thread Starter
    New Member
    Join Date
    Feb 2013
    Posts
    2

    Re: MS Excel Paste Data / Clipboard relative error?

    Quote Originally Posted by vbfbryce View Post
    You should be able to fix this by qualifying the copy sheet and the paste sheet (and the workbook(s) as well), something like this:

    Code:
    Sub pasteLink()
        Dim copyWB As Workbook
        Dim pasteWB As Workbook
        Dim copySheet As Worksheet
        Dim pasteSheet As Worksheet
        
        Set copyWB = Workbooks("copy1.xlsx")
        Set pasteWB = Workbooks("paste1.xlsx")
        Set copySheet = copyWB.Worksheets("sheet1")
        Set pasteSheet = pasteWB.Worksheets("sheet1")
        
        copySheet.Range("a1").Copy
        pasteSheet.Range("a1").PasteSpecial 'don't need to activate sheet in this case
        
        copySheet.Range("a2").Copy
        pasteSheet.Activate     'need to activate
        pasteSheet.Range("a2").Select   'select destination cell
        ActiveSheet.Paste link:=True
    End Sub
    Thanks for the tip. Looks like your code is working as far as activation of pasteSheet is not necessary. So copy/pastespecial works but "ActiveSheet.Paste Link:=True" sometimes causes 'No link to paste' error

Posting Permissions

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



Click Here to Expand Forum to Full Width