Copy & Paste - Unsafe?

[Nick H]

Hi, I'm hoping that an MVP can help settle a dispute (and save me some
reprogramming).

I have written a consolidation routine that takes data from a number
of formatted workbooks and consolidates it into a single 'Master_Data'
sheet.

As the code loops through each source workbook it defines the area to
be copied then copies and pastes it to the Master_Data sheet using the
following 2 lines of code...

rngAllocation.Copy

rngMaster.PasteSpecial Paste:=xlPasteValues, _
Operation:=xlNone, SkipBlanks:=False, Transpose:=False

During a code review, a colleague pointed out that this was unsafe
because another Excel application could be running at the same time on
the dual processor server and use the Windows paste buffer between my
code's copy and paste buffer operations, causing my code to paste the
wrong information.

I've never heard of this happening. Is there a slight risk?

Best regards, Nick H

 

[john]

Sorry, I can’t answer that question because it is not something that I have
ever experienced.

You can reduce the possibility of such an occurrence by adding:

Application.CutCopyMode=False to Clear The Clipboard after pastespecial
instruction.

Better still, try and avoid using copy & thus by-pass using the clipboard
altogether using an approach like following:

ringmaster.value = rngAllocation.Value

 

[Nick H]

I'd still be interested in an answer to the original question, if
anyone else can offer some insight?

However...

---snip---

rngmaster.value = rngAllocation.Value

---snip---

....thanks jb that does the job - and I'm kicking myself for not
thinking of it, having gone to all the trouble of defining both ranges
and making them the same size. Doh! Too much pressure to think
straight I guess

btw, if anyone else decides this is the method for them please note
that if your source range has multiple areas (as mine does actually)
then the destination range must have the same number of areas and they
must be the same size as the source areas. You then need to loop
through each area and equate them separately. Like so...

For i = 1 To rngAllocation.Areas.Count
rngMaster.Areas(i).Value = rngAllocation.Areas(i).Value
Next i

....the same goes for copy & paste but I was trying to keep the
original question simple. ;^)

 

[john]

Nick,
I pressed post before completing my earlier response - I was going to
mention that ranges should be same size but you have already covered. I alos
was going to suggest that you can use copy where needed like this:

rngAllocation.Copy Destination:=Sheet2.Range("A1")

which should also by-pass the clipboard.

Hope useful

 

[Nick H]

Nick,
I pressed post before completing my earlier response - I was going to
mention that ranges should be same size but you have already covered. I alos
was going to suggest that you can use copy where needed like this:

rngAllocation.Copy Destination:=Sheet2.Range("A1")

which should also by-pass the clipboard.

Except that method won't work with PasteSpecial - Thanks for your help
though John.

Br, Nick H