Copy Paste between workbooks fails intermittently

I am trying to copy from an external file and paste into the currently open file.

The code I'm using is below. Sometimes it works and sometimes it doesn't.

I'm still quite new to VBA and really not sure what the issue was... but I was suspecting the multiple connections to the same file. I first connect when I'm asking the user to select what sheet to use. I then connect again for each column to copy and paste. I tried setting up global variables for the worksheet and workbook variables so that I only had to SET them once. That seemed to work for a while and now the error is back.

You'll see that I am not doing a direct copy - paste (in one line). This always failed. I came up with a solution that was to Copy first, then select the active sheet and then the cell and finally just do an activesheet.paste. This was working but is now where the program fails intermittently with the error "Copy method of range class failed".

Other observations:

1. I can't get rid of the clipboard warning when the script ends despite using "Application.DisplayAlerts = False" as well as "Application.CutCopyMode = False" in several placees.
2. Sometimes when I go to the debug window, I can just click the "play" button and then the code finishes properly. Other times, it stays stuck on that activesheet.paste line.
3. When the code fails, I think there is a hanging open connection to the external WS. I figure this by seeing that when I close and open Excel, I get that external WS opening before the file I've selected. In this state, issue seems to happen more often.

Let me know if I can provide any other helpful information.

Thanks!

Please Login or Register  to view this content.

OK figured out why I was still getting the warning about the clipboard...

need this: oApp.CutCopyMode = False

Instead of this: Application.CutCopyMode = False

I realized I was setting the cutcopymode to false on the current application and not the one I had opened... duh!

Also, I realized that the loop will often work a couple times, pasting into a couple columns before failing. Often pressing the "play" button will get it to finish off the rest of the column ok.

No ideas?

I've tried so many variations of range and value and special pasting.. using variables or not, one line copy/paste, using "=" instead of copy paste etc.. etc...

Here's something that I finally tried in desperation that seems to actually be working so far:

Added a timer just before the paste.

Giving it a 1 sec delay before pasting seems to have resolved my issue!

Maybe that helps identify the real problem???

Here's the code I'm using to wait:

Application.Wait (Now + TimeValue("0:00:01"))

I have found that using 'activesheet.paste' will cause problems sometimes. I was always able to cure the problem by using 'activesheet.PasteSpecial' instead