-
Feb 1st, 2013, 05:05 AM
#1
Thread Starter
New Member
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!
-
Feb 1st, 2013, 07:49 AM
#2
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
-
Feb 4th, 2013, 07:44 AM
#3
Thread Starter
New Member
Re: MS Excel Paste Data / Clipboard relative error?
Originally Posted by vbfbryce
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
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|