Deleting blue paste area

  • Thread starter Thread starter Otto Moehrbach
  • Start date Start date
O

Otto Moehrbach

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

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

See the other responses you received for tips.


Gord

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 MS Excel MVP
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Back
Top