I've noticed that when I run a copy and paste operation in VBA using
statements of the form

Worksheet1.Range1.Copy
Worksheet2.Range2.pastespecial

that target range, Range2, remains selected unless I perform some other
operation on a range in Worksheet2.

I would like to unselect that range after the paste method is executed, but
I'd rather not use a Select method at that point in my code, because I don't
want to make Worksheet2 the active sheet. Is there a way to deselect that
highlighted range without using a Select statement?

Thanks in advance.

Paul

Hi Paul,

Yes, there is a way to deselect the highlighted range without using a Select statement. You can use the Application.CutCopyMode property to clear the copy buffer, which will automatically deselect the range.

Here's an example of how you can modify your code to include this:

1. Formula:

   Worksheet1.Range1.Copy 

2. Formula:

   Worksheet2.Range2.PasteSpecial 

3. Formula:

   Application.CutCopyMode = False 


This will clear the copy buffer and deselect the range in Worksheet2 without making it the active sheet.

I don't believe there is a way without switching to the second sheet and
switching back.

--

Vasant


"Paul James" wrote in message
...
I've noticed that when I run a copy and paste operation in VBA using
statements of the form

Worksheet1.Range1.Copy
Worksheet2.Range2.pastespecial

that target range, Range2, remains selected unless I perform some other
operation on a range in Worksheet2.

I would like to unselect that range after the paste method is executed,
but
I'd rather not use a Select method at that point in my code, because I
don't
want to make Worksheet2 the active sheet. Is there a way to deselect that
highlighted range without using a Select statement?

Thanks in advance.

Paul

Paul,
If you are transferring values, I think this will work without selecting
the range:

Worksheet2.Range2.Value = Worksheet1.Range1.Value

I also know that:
Worksheet1.Range1.Copy Worksheet2.Range2

will work, but this is not PasteSpecial, of course.

Alex J

"Paul James" wrote in message
...
I've noticed that when I run a copy and paste operation in VBA using
statements of the form

Worksheet1.Range1.Copy
Worksheet2.Range2.pastespecial

that target range, Range2, remains selected unless I perform some other
operation on a range in Worksheet2.

I would like to unselect that range after the paste method is executed,
but
I'd rather not use a Select method at that point in my code, because I
don't
want to make Worksheet2 the active sheet. Is there a way to deselect that
highlighted range without using a Select statement?

Thanks in advance.

Paul

Paul,

This worked for me from recording a Macro such as you described then
checking the code:

Application.CutCopyMode = False

-gk-


"Paul James" wrote in message
...
I've noticed that when I run a copy and paste operation in VBA using
statements of the form

Worksheet1.Range1.Copy
Worksheet2.Range2.pastespecial

that target range, Range2, remains selected unless I perform some other
operation on a range in Worksheet2.

I would like to unselect that range after the paste method is executed,
but
I'd rather not use a Select method at that point in my code, because I
don't
want to make Worksheet2 the active sheet. Is there a way to deselect that
highlighted range without using a Select statement?

Thanks in advance.

Paul

That will work for me. Good idea.

Thanks, Alex.

Paul

Thanks for the response, gk.

Unfortunately, while Application.cutcopymode = false turns off the
highlighted marquee from the range that was copied from, but it doesn't
deselect the range that was copied to.

See Alex's suggestion elsewhere in this thread for a way to get the desired
effect.

Paul