Bug - Programmatic copy/paste other sheet to activecell

[Greg Wilson]

Does anyone have an elegant fix to the following:
1. Put text in any cell in Sheet2, say cell A1.
2. Select a cell in Sheet1.
3. Run this code:
Sub Test()
Sheets(2).Range("A1").Copy ActiveCell
End Sub
4. Now select a different cell in Sheet1. I find that the black border
highlight of the former activecell (cell that was pasted to) acts in reverse
- i.e. if you select it, the black border goes blank and if you select a
differenct cell it receives a black border. Therefore, two cells now have
black borders.

You can fix it with a kludge:
Sub Test()
Sheets(2).Range("A1").Copy ActiveCell
ActiveCell.Copy ActiveCell
End Sub

You can also fix it by scrolling until the cell is off screen and then
scrolling back; or seleting a multicell range that completely contains the
affected cell, and then select the cell.

If I don't receive a better solution then this will at least serve as a bug
warning. Wondering if there is a more elegant solution (repainting?) or if
I'm missing something. Appreciative of your responses.

Greg

 

[NickHK]

Greg,
Yes, I see the same behaviour in XL2K & XL2002.
Seems to have been around for a while, possibly related to the mouse driver:
http://www.mvps.org/dmcritchie/excel/ghosting.txt

NickHK

 

[Incidental]

Hi Greg

I'm not sure why excel would be doing that but it does it on my system
to. It will work if you pass the cell value to a string then put the
string to the activecell like the code below.

Option Explicit
Dim MyStr As String

Sub Test()
MyStr = Sheets(2).Range("A1")
ActiveCell.Value = MyStr
End Sub

Hope this is of some use to you.

S

 

[Greg Wilson]

Thanks Nick. I gusess my solution is as good as any other.

Greg

 

[Greg Wilson]

Thanks for your response. Unfortunately I also need the formats and so
copying is the best option.

It appears to be caused by the copy/paste operation. You don't actually need
to pass the value to a variable. For example, this doesn't cause the problem:

ActiveCell.Value = Sheets("Sheet2").Range("A1").Value

while this does:

Sheets(2).Range("A1").Copy ActiveCell

Regards,
Greg