I have a macro that copies and pastes fields, line by line into a different program open at the same time as Excel. Basically it tabs through the spreadsheet copying, then it switches focus to the 3rd party module and pastes the information into the module, tabs to the next field then goes back to the spreadsheet to copy the next field.
This seems to work except that it tends to get bogged down eventually and starts making mistakes when copying and pasting. I added some delays into the code but no matter how long I make them, I still see problems copying and pasting.
Is it bogging down because I am not purging the clipboard or something? Does anyone have any recommendations? I'm not even sure if purging the clipboard is necessary or possible. How nice it would be if they would just allow us to upload a CSV or Tab delimited text file to post, but no such luck
With regard to purging the clipboard, this is the line of code placed prior to the code doing a new copy.
Application.CutCopyMode = False
If that fails, you might look to see if there is a specific failure point, and if there is,
add a counter to the VBA code and record its value in a cell as you loop through the process,
and an If...End If block of code to go into debug when the counter reaches
(or just about to reach) the failure point. At that point, you examine what is
being copied and is the copy accurate. However, using debug may fail to find the problem because
you're stepping through the VBA code slowly, which may prevent the data-copying between two applications from tripping over itself.