How to copy/paste without using clipboard?

[Ming]

I'm using Excel to generate some reports in background which could take long
time. The problem is that a lot of copy/paste is used in the template for
generating those reports which makes the Window clipboard unusable for the
user working in foreground.

There are two ways you can copy/paste data around in Excel.

One is Selection.Copy and ActiveSheet.Paste. It copies the content in
Windows clipboard which can be shared by other applications. I can understand
why this function uses clipboard.

There second way is to use Range.Copy [Destination] to copy and paste in one
function call. This function also uses clipboard. Actually, it copies into
clipboard and clears it right away. So, the content can't be shared at all.
Therefore, there is really no reason to use the clipboard. Sadly, this is the
way it behaves.

There is another similar function Worksheet.Copy [Destination] which doesn't
use clipboard.

Anybody is aware of workaround for this problem?

 

[Mike H]

Hi,

Apart from saying there is a problem in using the clipboard you never quite
got around to telling us what that problem is.

Anyway, this doesn't use the clipboard

Sheet2.Range("A1:a100").copy Sheet1.Range("B1")


Mike

 

[Jacob Skaria]

Probably a VBA solution would help..

If this post helps click Yes

 

[Mike H]

Hmm,

Forget that, it does use the clipboard.

Hi,

Apart from saying there is a problem in using the clipboard you never quite
got around to telling us what that problem is.

Anyway, this doesn't use the clipboard

Sheet2.Range("A1:a100").copy Sheet1.Range("B1")


Mike

I'm using Excel to generate some reports in background which could take long
time. The problem is that a lot of copy/paste is used in the template for
generating those reports which makes the Window clipboard unusable for the
user working in foreground.

There are two ways you can copy/paste data around in Excel.

One is Selection.Copy and ActiveSheet.Paste. It copies the content in
Windows clipboard which can be shared by other applications. I can understand
why this function uses clipboard.

There second way is to use Range.Copy [Destination] to copy and paste in one
function call. This function also uses clipboard. Actually, it copies into
clipboard and clears it right away. So, the content can't be shared at all.
Therefore, there is really no reason to use the clipboard. Sadly, this is the
way it behaves.

There is another similar function Worksheet.Copy [Destination] which doesn't
use clipboard.

Anybody is aware of workaround for this problem?

 

[Ming]

Hi Mike and Jacob, thank you very much for quick responses.

Mike, I'm sorry I wasn't clear enough on my problem. The problem is that,
while Excel is working in background generating reports, user working on
foreground (e.g. writing emails or word documents) cannot use copy/paste.
Sometimes, you get wrong thing pasted if the Excel template was doing
Selection.Copy, and sometimes you don't get anything pasted if Excel was
doing Range.Copy (which clears everything in the clipboard right away). There
is also a potential that wrong thing is pasted to the Excel report. Just a
timing issue.

As I said in my initial post, Range.Copy does use clipboard - it just clears
the clipboard. You can try it out easily. Microsoft's document on that
function is wrong.

Jacob, I'm using VBA code to do that. In the code both Selection.Copy and
Range.Copy are used right now. But if there is any copy function whick
doesn't interfere with the clipboard, I would refactor my VBA code to use
that.

Probably a VBA solution would help..

If this post helps click Yes
---------------
Jacob Skaria

I'm using Excel to generate some reports in background which could take long
time. The problem is that a lot of copy/paste is used in the template for
generating those reports which makes the Window clipboard unusable for the
user working in foreground.

There are two ways you can copy/paste data around in Excel.

One is Selection.Copy and ActiveSheet.Paste. It copies the content in
Windows clipboard which can be shared by other applications. I can understand
why this function uses clipboard.

There second way is to use Range.Copy [Destination] to copy and paste in one
function call. This function also uses clipboard. Actually, it copies into
clipboard and clears it right away. So, the content can't be shared at all.
Therefore, there is really no reason to use the clipboard. Sadly, this is the
way it behaves.

There is another similar function Worksheet.Copy [Destination] which doesn't
use clipboard.

Anybody is aware of workaround for this problem?

 

[JP]

To the OP: Use the Office Clipboard instead. See
http://blogs.techrepublic.com.com/msoffice/?p=544

Mike: I think the problem is either that the worksheet might have
sensitive data on it, which when copied, is available publicly via the
Windows clipboard (hence the comment by the OP: "the content in
Windows clipboard which can be shared by other applications"), or that
Excel VBA is running while the user completes other tasks, and the
clipboard is unavailable to that user because it is being used by VBA
(hence the comment by the OP: "which makes the Window clipboard
unusable for the user working in foreground.")

I'm curious to know which is correct.

--JP

Hi,

Apart from saying there is a problem in using the clipboard you never quite
got around to telling us what that problem is.

Anyway, this doesn't use the clipboard

Sheet2.Range("A1:a100").copy Sheet1.Range("B1")

Mike

I'm using Excel to generate some reports in background which could takelong
time. The problem is that a lot of copy/paste is used in the template for
generating those reports which makes the Window clipboard unusable for the
user working in foreground.

There are two ways you can copy/paste data around in Excel.

One is Selection.Copy and ActiveSheet.Paste. It copies the content in
Windows clipboard which can be shared by other applications. I can understand
why this function uses clipboard.

There second way is to use Range.Copy [Destination] to copy and paste in one
function call. This function also uses clipboard. Actually, it copies into
clipboard and clears it right away. So, the content can't be shared at all.
Therefore, there is really no reason to use the clipboard. Sadly, this is the
way it behaves.

There is another similar function Worksheet.Copy [Destination] which doesn't
use clipboard.

Anybody is aware of workaround for this problem?- Hide quoted text -

- Show quoted text -

 

[Jacob Skaria]

To copy values try the below

Sheets("Sheet2").Range("A11:B15") = Sheets("Sheet1").Range("A1:B5").Value

OR

Dim rngTemp As Range
Set rngTemp = Sheets("Sheet1").Range("A1:B5")
Sheets("Sheet2").Range("A11").Resize( _
rngTemp.Rows.Count, rngTemp.Columns.Count) = rngTemp.Value


If this post helps click Yes
---------------
Jacob Skaria

Hi Mike and Jacob, thank you very much for quick responses.

Mike, I'm sorry I wasn't clear enough on my problem. The problem is that,
while Excel is working in background generating reports, user working on
foreground (e.g. writing emails or word documents) cannot use copy/paste.
Sometimes, you get wrong thing pasted if the Excel template was doing
Selection.Copy, and sometimes you don't get anything pasted if Excel was
doing Range.Copy (which clears everything in the clipboard right away). There
is also a potential that wrong thing is pasted to the Excel report. Just a
timing issue.

As I said in my initial post, Range.Copy does use clipboard - it just clears
the clipboard. You can try it out easily. Microsoft's document on that
function is wrong.

Jacob, I'm using VBA code to do that. In the code both Selection.Copy and
Range.Copy are used right now. But if there is any copy function whick
doesn't interfere with the clipboard, I would refactor my VBA code to use
that.

Probably a VBA solution would help..

If this post helps click Yes
---------------
Jacob Skaria

I'm using Excel to generate some reports in background which could take long
time. The problem is that a lot of copy/paste is used in the template for
generating those reports which makes the Window clipboard unusable for the
user working in foreground.

There are two ways you can copy/paste data around in Excel.

One is Selection.Copy and ActiveSheet.Paste. It copies the content in
Windows clipboard which can be shared by other applications. I can understand
why this function uses clipboard.

There second way is to use Range.Copy [Destination] to copy and paste in one
function call. This function also uses clipboard. Actually, it copies into
clipboard and clears it right away. So, the content can't be shared at all.
Therefore, there is really no reason to use the clipboard. Sadly, this is the
way it behaves.

There is another similar function Worksheet.Copy [Destination] which doesn't
use clipboard.

Anybody is aware of workaround for this problem?

 

[Ming]

Hi Jocab, this is the workaround I'm trying to do now. But it is painfully
slow. Plus, it's difficult to do everything what copy/paste offers (e.g. I
didn't figure out how to duplicate formula with relative reference of cells,
and how to duplicate merged cells).

To copy values try the below

Sheets("Sheet2").Range("A11:B15") = Sheets("Sheet1").Range("A1:B5").Value

OR

Dim rngTemp As Range
Set rngTemp = Sheets("Sheet1").Range("A1:B5")
Sheets("Sheet2").Range("A11").Resize( _
rngTemp.Rows.Count, rngTemp.Columns.Count) = rngTemp.Value


If this post helps click Yes
---------------
Jacob Skaria

Hi Mike and Jacob, thank you very much for quick responses.

Mike, I'm sorry I wasn't clear enough on my problem. The problem is that,
while Excel is working in background generating reports, user working on
foreground (e.g. writing emails or word documents) cannot use copy/paste.
Sometimes, you get wrong thing pasted if the Excel template was doing
Selection.Copy, and sometimes you don't get anything pasted if Excel was
doing Range.Copy (which clears everything in the clipboard right away). There
is also a potential that wrong thing is pasted to the Excel report. Just a
timing issue.

As I said in my initial post, Range.Copy does use clipboard - it just clears
the clipboard. You can try it out easily. Microsoft's document on that
function is wrong.

Jacob, I'm using VBA code to do that. In the code both Selection.Copy and
Range.Copy are used right now. But if there is any copy function whick
doesn't interfere with the clipboard, I would refactor my VBA code to use
that.

Probably a VBA solution would help..

If this post helps click Yes
---------------
Jacob Skaria


:

I'm using Excel to generate some reports in background which could take long
time. The problem is that a lot of copy/paste is used in the template for
generating those reports which makes the Window clipboard unusable for the
user working in foreground.

There are two ways you can copy/paste data around in Excel.

One is Selection.Copy and ActiveSheet.Paste. It copies the content in
Windows clipboard which can be shared by other applications. I can understand
why this function uses clipboard.

There second way is to use Range.Copy [Destination] to copy and paste in one
function call. This function also uses clipboard. Actually, it copies into
clipboard and clears it right away. So, the content can't be shared at all.
Therefore, there is really no reason to use the clipboard. Sadly, this is the
way it behaves.

There is another similar function Worksheet.Copy [Destination] which doesn't
use clipboard.

Anybody is aware of workaround for this problem?

 

[JP]

I'm not going to debate copy/paste usage. My only other suggestions
are workarounds:

1) Can you do something else while Excel is processing?
2) Can you rewrite your code so it runs better/faster?
3) Can you run the code from a dedicated "Reports Only" machine?

--JP

 

[Ming]

Hi JP, the "workaround" we current have is to put a note in the release note
saying "don't use windows clipboard while xxx is generating report in
background". And I already hear people responding "this is unacceptable".

1) Can you do something else while Excel is processing?
Yes, you can do anything else but just don't use windows clipboard while
Excel is generating reports in background which could take hours.

2) Can you rewrite your code so it runs better/faster?
Yes, this is always what we try to do. But it simply takes time to generate
and print the reports.

3) Can you run the code from a dedicated "Reports Only" machine?
This is another advice we give to user, but it'd odd to many people - what?
I need another machine for that?

 

[Jacob Skaria]

Yes; you will have to deal with that (formats,formulas,borders etc one by
one

If this post helps click Yes
---------------
Jacob Skaria

Hi Jocab, this is the workaround I'm trying to do now. But it is painfully
slow. Plus, it's difficult to do everything what copy/paste offers (e.g. I
didn't figure out how to duplicate formula with relative reference of cells,
and how to duplicate merged cells).

To copy values try the below

Sheets("Sheet2").Range("A11:B15") = Sheets("Sheet1").Range("A1:B5").Value

OR

Dim rngTemp As Range
Set rngTemp = Sheets("Sheet1").Range("A1:B5")
Sheets("Sheet2").Range("A11").Resize( _
rngTemp.Rows.Count, rngTemp.Columns.Count) = rngTemp.Value


If this post helps click Yes
---------------
Jacob Skaria

Hi Mike and Jacob, thank you very much for quick responses.

Mike, I'm sorry I wasn't clear enough on my problem. The problem is that,
while Excel is working in background generating reports, user working on
foreground (e.g. writing emails or word documents) cannot use copy/paste.
Sometimes, you get wrong thing pasted if the Excel template was doing
Selection.Copy, and sometimes you don't get anything pasted if Excel was
doing Range.Copy (which clears everything in the clipboard right away). There
is also a potential that wrong thing is pasted to the Excel report. Just a
timing issue.

As I said in my initial post, Range.Copy does use clipboard - it just clears
the clipboard. You can try it out easily. Microsoft's document on that
function is wrong.

Jacob, I'm using VBA code to do that. In the code both Selection.Copy and
Range.Copy are used right now. But if there is any copy function whick
doesn't interfere with the clipboard, I would refactor my VBA code to use
that.

:

Probably a VBA solution would help..

If this post helps click Yes
---------------
Jacob Skaria


:

I'm using Excel to generate some reports in background which could take long
time. The problem is that a lot of copy/paste is used in the template for
generating those reports which makes the Window clipboard unusable for the
user working in foreground.

There are two ways you can copy/paste data around in Excel.

One is Selection.Copy and ActiveSheet.Paste. It copies the content in
Windows clipboard which can be shared by other applications. I can understand
why this function uses clipboard.

There second way is to use Range.Copy [Destination] to copy and paste in one
function call. This function also uses clipboard. Actually, it copies into
clipboard and clears it right away. So, the content can't be shared at all.
Therefore, there is really no reason to use the clipboard. Sadly, this is the
way it behaves.

There is another similar function Worksheet.Copy [Destination] which doesn't
use clipboard.

Anybody is aware of workaround for this problem?

 

[Bob I]

Have the PC run the reports after hours.