How can I deselect after paste special

[DJay]

Using Excel 2003. I have a VBA program that must copy ONLY the FORMULAS and
FORMATS form a given row to a SELECTED range. The paste works fine, but when
the paste finishes, the target cells are still "selected" and have the
selection border around them. I have used Application.CutCopyMode = False to
turn off the selection on the SOURCE cell (which DOES work) and have tried
all of the following to de-select the target cells:
1. select a new area
2. range (1,1)=range(1,2)
3. re-activate the sheet
4. SendKeys {ESC} (also HOME and LEFT)
5. actiivate a ifferent sheet and re-activate the current sheet

None of these de-selects the target area, but if I click on the actual
sheet, I can use ESC, left-arrow, HOME, etc to cause the de-select. ANY
"select" statements following the paste special, cause an error. Any clues.
(I cannot use simple COPY as I do NOT want to copy the data - ONLY formulas
and formats.)

Any hints would be appreciated.

 

[Jim Cone]

Two things to consider...
You cannot select cells on a sheet unless that sheet is the active sheet.
You do not have to select cells in order to paste into them.
--
Jim Cone
San Francisco, USA
http://www.realezsites.com/bus/primitivesoftware


"DJay" <[email protected]>
wrote in message
Using Excel 2003. I have a VBA program that must copy ONLY the FORMULAS and
FORMATS form a given row to a SELECTED range. The paste works fine, but when
the paste finishes, the target cells are still "selected" and have the
selection border around them. I have used Application.CutCopyMode = False to
turn off the selection on the SOURCE cell (which DOES work) and have tried
all of the following to de-select the target cells:
1. select a new area
2. range (1,1)=range(1,2)
3. re-activate the sheet
4. SendKeys {ESC} (also HOME and LEFT)
5. actiivate a ifferent sheet and re-activate the current sheet

None of these de-selects the target area, but if I click on the actual
sheet, I can use ESC, left-arrow, HOME, etc to cause the de-select. ANY
"select" statements following the paste special, cause an error. Any clues.
(I cannot use simple COPY as I do NOT want to copy the data - ONLY formulas
and formats.)

Any hints would be appreciated.

 

[Tom Ogilvy]

Assuming the sheet pasted to is the activesheet then

Range("A1").Select

always works for me.

 

[DJay]

PasteSpecial does NOT appear to work using: range ().PasteSpecial. Every
syntax trick I could find failed and I had to do a Copy first, then a Select
of the target, then a Secection.PasteSpecial xlPasteFormulas. I am copying
from a 1-row range with a specific # of columns and Pasting Special into a 2
(or more) row range of the exact same size.

Also, I have tried the Range("A1").Select and it gets trapped by the
debugger and the target range is still selected. Going to the worksheet
manually and pressing any keys (such as HOME, any cursor, END, etc) turns if
off.

 

[FSt1]

hi,
i think tom has the best solution for you. try something like this.....
Sub Mactest()

Sheets("sheet1").Select
Range("B55").Copy
Sheets("sheet2").Range("B5").PasteSpecial xlPasteFormulas
Sheets("sheet2").Select
Range("A1").Select
Sheets("sheet1").Select
Application.CutCopyMode = False
Range("A1").Select
End Sub

regards
FSt1

 

[Gary Keramidas]

another way:

Sub test()
Dim ws As Worksheet
Dim ws2 As Worksheet
Set ws = Worksheets("Sheet1")
Set ws2 = Worksheets("Sheet2")
Application.ScreenUpdating = False

With ws
.Range("B55").Copy
ws2.Range("b5").PasteSpecial xlPasteFormulas
End With

Application.CutCopyMode = False
ws.Select
ws2.Select False
Range("A1").Select
ws.Select
Application.ScreenUpdating = True

End Sub

 

[Tom Ogilvy]

It does work with a range object. The problems all appear to be with your
code. I think you have gotten yourself wrapped around the axle with
multiple misperceptions. Look at Gary's suggestion.

 

[DJay]

Thanks to all for the tips. When noe of them worked, I decided ot back up in
my function and discovered that my original Insertion rows were stillactive,
not my "copy" targets. SO I re-did my insertion using:
Rows(myInsertionPt).Resize(count).Insert shift:=xlDown

after which I was able to use several of the preceding tips to both improve
my pastespecial and the performance of my code.

Thanks to ALL of you, VERY much.