Excel XP & Win XP
I am operating within a 'With ws/End With' construct, where ws is a
worksheet. I copy a range from somewhere else and paste it to ws. My copy
paste commands are:
ThisRange.copy
..ThisCell.PasteSpecial xlPasteValues

When the code is through and I manually select sheet ws, I see that the
paste range is still all blue from the paste action. I can take all the
blue out by simply selecting any cell, of course. But my question is: Can
I get rid of the blue in the paste range of ws by code, after the paste
command, without selecting ws and selecting a cell?
Thanks for your time. Otto

Hi Otto

I always select the first cell but this only work if the Dest sheet is active

With dest.Sheets(1)
.Cells(1).PasteSpecial Paste:=8
' Paste:=8 will copy the column width in Excel 2000 and higher
' If you use Excel 97 use the other example
.Cells(1).PasteSpecial xlPasteValues, , False, False
.Cells(1).PasteSpecial xlPasteFormats, , False, False
.Cells(1).Select
Application.CutCopyMode = False
End With


--
Regards Ron de Bruin
http://www.rondebruin.nl



"Otto Moehrbach" <(E-Mail Removed)> wrote in message news:(E-Mail Removed)...
> Excel XP & Win XP
> I am operating within a 'With ws/End With' construct, where ws is a worksheet. I copy a range from somewhere else and paste it to
> ws. My copy paste commands are:
> ThisRange.copy
> .ThisCell.PasteSpecial xlPasteValues
>
> When the code is through and I manually select sheet ws, I see that the paste range is still all blue from the paste action. I
> can take all the blue out by simply selecting any cell, of course. But my question is: Can I get rid of the blue in the paste
> range of ws by code, after the paste command, without selecting ws and selecting a cell?
> Thanks for your time. Otto
>

Otto

How about Application.CutCopyMode = False


Gord Dibben MS Excel MVP

On Tue, 10 Oct 2006 16:31:53 -0400, "Otto Moehrbach" <(E-Mail Removed)>
wrote:

>Excel XP & Win XP
>I am operating within a 'With ws/End With' construct, where ws is a
>worksheet. I copy a range from somewhere else and paste it to ws. My copy
>paste commands are:
>ThisRange.copy
>.ThisCell.PasteSpecial xlPasteValues
>
>When the code is through and I manually select sheet ws, I see that the
>paste range is still all blue from the paste action. I can take all the
>blue out by simply selecting any cell, of course. But my question is: Can
>I get rid of the blue in the paste range of ws by code, after the paste
>command, without selecting ws and selecting a cell?
>Thanks for your time. Otto
>

As far as I can tell you have to have a cell selected in the sheet. Try
this code at the end of you code(you'll need to modify), it's will get
rid of the "blued" out area and then return to the sheet you were on

' ws is the sheet you have the blue on
' you'll need to change the sheet name
' ws 2 is the active sheet
'
Sub Clear_The_Blue()
Dim ws, ws2 As Worksheet
Set ws = Worksheets("Sheet2")
Set ws2 = ActiveSheet
ws.Activate
ws.Cells(1, 1).Select
ws2.Activate
End Sub


Sandy

Otto Moehrbach wrote:
> Excel XP & Win XP
> I am operating within a 'With ws/End With' construct, where ws is a
> worksheet. I copy a range from somewhere else and paste it to ws. My copy
> paste commands are:
> ThisRange.copy
> .ThisCell.PasteSpecial xlPasteValues
>
> When the code is through and I manually select sheet ws, I see that the
> paste range is still all blue from the paste action. I can take all the
> blue out by simply selecting any cell, of course. But my question is: Can
> I get rid of the blue in the paste range of ws by code, after the paste
> command, without selecting ws and selecting a cell?
> Thanks for your time. Otto

Gord
That doesn't do anything with the destination sheet for me. It removes
the running ants around the source range, but nothing else. The blue in the
destination range is still there. Am I missing something? Thanks for your
time. Otto
"Gord Dibben" <gorddibbATshawDOTca> wrote in message
news:(E-Mail Removed)...
> Otto
>
> How about Application.CutCopyMode = False
>
>
> Gord Dibben MS Excel MVP
>
> On Tue, 10 Oct 2006 16:31:53 -0400, "Otto Moehrbach"
> <(E-Mail Removed)>
> wrote:
>
>>Excel XP & Win XP
>>I am operating within a 'With ws/End With' construct, where ws is a
>>worksheet. I copy a range from somewhere else and paste it to ws. My
>>copy
>>paste commands are:
>>ThisRange.copy
>>.ThisCell.PasteSpecial xlPasteValues
>>
>>When the code is through and I manually select sheet ws, I see that the
>>paste range is still all blue from the paste action. I can take all the
>>blue out by simply selecting any cell, of course. But my question is:
>>Can
>>I get rid of the blue in the paste range of ws by code, after the paste
>>command, without selecting ws and selecting a cell?
>>Thanks for your time. Otto
>>
>

Otto

Apologies. I guess you have to select a cell somewhere.

See the other responses you received for tips.


Gord

On Tue, 10 Oct 2006 17:30:11 -0400, "Otto Moehrbach" <(E-Mail Removed)>
wrote:

>Gord
> That doesn't do anything with the destination sheet for me. It removes
>the running ants around the source range, but nothing else. The blue in the
>destination range is still there. Am I missing something? Thanks for your
>time. Otto
>"Gord Dibben" <gorddibbATshawDOTca> wrote in message
>news:(E-Mail Removed)...
>> Otto
>>
>> How about Application.CutCopyMode = False
>>
>>
>> Gord Dibben MS Excel MVP
>>
>> On Tue, 10 Oct 2006 16:31:53 -0400, "Otto Moehrbach"
>> <(E-Mail Removed)>
>> wrote:
>>
>>>Excel XP & Win XP
>>>I am operating within a 'With ws/End With' construct, where ws is a
>>>worksheet. I copy a range from somewhere else and paste it to ws. My
>>>copy
>>>paste commands are:
>>>ThisRange.copy
>>>.ThisCell.PasteSpecial xlPasteValues
>>>
>>>When the code is through and I manually select sheet ws, I see that the
>>>paste range is still all blue from the paste action. I can take all the
>>>blue out by simply selecting any cell, of course. But my question is:
>>>Can
>>>I get rid of the blue in the paste range of ws by code, after the paste
>>>command, without selecting ws and selecting a cell?
>>>Thanks for your time. Otto
>>>
>>
>

Gord Dibben MS Excel MVP