Deleting blue paste area
 

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 a
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

Deleting blue paste area
 

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" wrote in message ...
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 a
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

Deleting blue paste area
 

Otto

How about Application.CutCopyMode = False


Gord Dibben MS Excel MVP

On Tue, 10 Oct 2006 16:31:53 -0400, "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 a
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

Deleting blue paste area
 

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 a
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

Deleting blue paste area
 

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
...
Otto

How about Application.CutCopyMode = False


Gord Dibben MS Excel MVP

On Tue, 10 Oct 2006 16:31:53 -0400, "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 a
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

Deleting blue paste area
 

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"
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
.. .
Otto

How about Application.CutCopyMode = False


Gord Dibben MS Excel MVP

On Tue, 10 Oct 2006 16:31:53 -0400, "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 a
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