Bug - Programmatic copy/paste other sheet to activecell

G

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
 
I

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
 
G

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
 

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

Top