Strange behavior when using copy paste in VBA

**markinpt** · 01-30-2013, 02:15 PM

Newbie here, glad I found the forum and hope someone can help with the very strange problem.

Background

Last summer, I got a new PC with Office 2010 already installed. Throughout the years, I have done a lot of VBA programming but I have never seen this issue. On to the problem.

I have created several VBA programs which analyze other spreadsheets and when I detect a record in the spreadsheet that I want to save, I copy that line in the scanned spreadsheet into a new spreadsheet. All of the macros are different but seem to have the same issue.

During some of the row copy and pastes, a windows explorer window automatically opens to my windows SkyDrive folder (I have the desktop version of SkyDrive installed). It is a completely random event and even if I run the application again, it doesn't occur at the same location. I have attempted to re-write the copy paste command in several different forms but nothing has prevented this from happening. I believe it is a bug somewhere in Excel VBA and/or SkyDrive (no other folder opens automatically). BTW, there are no references to the SkyDrive folder in anything I am coping.

Anyway, just curious if anyone else has experienced this very strange behavior. Thanks

**xladept** · 01-30-2013, 02:25 PM

I use 2003 but it has a quirk depending on whether the calculation setting is automatic or manual - you might try toggling your calculation setting to see if that's the reason!

**markinpt** · 01-31-2013, 11:11 AM

Thanks for the suggestion but I had already tried that one. I also tried to disable alerts and few other things. The problem is still there.

**JosephP** · 01-31-2013, 11:45 AM

I have never heard of such a problem unless you are copying formulas that refer to non-existent locations

**markinpt** · 01-31-2013, 12:22 PM

There are only strings and numbers in the cells that I am copying. They are going from one page of same workbook to another page. I believe it to be an interaction with Windows SkyDrive. Not sure why though.

I have also tried using the clipboard copy and it does the same thing. The code copies several hundred rows which may have something to do with it because it doesn't exhibit this problem unless there are a lot of rows to copy.

**xladept** · 01-31-2013, 01:06 PM

Hi markinpt,

Maybe it's the clipboard - you could assign your input records to a variant and then - assign the variant to the output range - it would be faster than the clipboard and might evade the conflict!

**markinpt** · 01-31-2013, 01:32 PM

Actually, the current version is not using the clipboard. I tried it both ways. I do a direct copy via:

row1.Cells.Copy Destination:=dWS.Cells(dRow, 1)

According to the Excel VBA documentation, that method does not use the clipboard. Plus I also need the formatting and I really didn't want to transfer the format for each cell individually although that might fix the problem. I can live with the windows popping up but I was curious why it was doing that.

**xladept** · 01-31-2013, 02:39 PM

I suppose that this wouldn't fix it either????

Please Login or Register  to view this content.

**markinpt** · 02-01-2013, 11:12 AM

Aren't they the same call? I thought the keyword "Destination" was optional. I will try it without the keyword and see if it makes a difference.

**JosephP** · 02-01-2013, 12:33 PM

yes they are the same call

**xladept** · 02-01-2013, 01:45 PM

I'm just reaching at straws - you never know what doesn't work the way it should!

**markinpt** · 02-05-2013, 09:28 PM

Same Result

**markinpt** · 09-22-2016, 07:56 PM

UPDATE -- 3 years later, I am now on Windows 10, Office 2016. a new PC, SkyDrive is now OneDrive and I am still getting the same problem when copying and pasting. I only do this when debugging so it isn't a huge problem but it is just the weirdest thing. It only happens for a couple of the copy and pastes (6-10 out of 1000s).

Just wondering if after all this time, anyone else has seen this.

**xladept** · 09-22-2016, 08:22 PM

I just wonder whether any of us could replicate that - could you post something for us to try?

**markinpt** · 09-23-2016, 10:29 AM

I will see if I can create a stripped down version that exhibits the same behavior and is repeatable.

**markinpt** · 09-23-2016, 04:11 PM

Ok, I have a really stripped down version that seems to cause the issue fairly repeatably. But only the first time it is run after opening the workbook. After that, it no longer opens up OneDrive folders.

It doesn't seem to matter if the workbook is on onedrive or not but of course you have to have onedrive running.

https://www.dropbox.com/s/kglfmowlnu...v001.xlsm?dl=1

Looking forward to see if anyone else exhibits the same behavior.

**Winon** · 10-07-2016, 01:36 AM

Hello markinpt,

I do not have OneDrive, but please try the attached sample, and see if that works for you.

**markinpt** · 10-07-2016, 10:32 AM

This is a single copy and paste of the entire tab which will not work for my needs but thanks anyway. I have simplified the actual macro quite a bit but the original picks and chooses which rows to copy out of hundreds of tabs. It doesn't actually copy an entire tab.