Remove 'marching ants' from copied cell

**staggers47** · 01-13-2013, 11:38 AM

Hi,

I have a button with a single line of code to copy the contents of a cell for a user to paste elsewhere (probably a Word doc).

How can I make the 'marching ants' vanish from around the cell that was copied.

If I add a line to the button macro to make cut/copy mode false it does remove them, but also removes the value from the clipboard!

How can I get round this?

Help appreciated.

**Ron Coderre** · 01-13-2013, 11:49 AM

Two suggestions:
• Store the value(s) in named constants...(similar to named ranges, but instead of a range reference you put the value you want to store)
• Store the value(s) someplace in the workbook for later retrieval

Does that help?

**HaHoBe** · 01-13-2013, 11:55 AM

Hi, staggers47,

I´d suggest to copy the ranges directly via Value. The command needed could be either of these two (they both work equally)

Please Login or Register  to view this content.

Ciao,
Holger

**MarvinP** · 01-13-2013, 12:00 PM

Hi staggers47,

Try this:
If the cell to copy is C2 then instead of doing a
Range("C2").Copy alone, Try a,
Rance("C2").Copy Destination:=Range("C2") instead.

**staggers47** · 01-13-2013, 12:06 PM

Haha!

Hi again Ron. Used your second idea. Put it in a sheet the user will never go to and then copied it from there. It had the ants, but it doesn't matter there. And as soon as a new word turns up the ants go away. And on the original screen they're using, they don't appear in the first place, of course, which is the important thing.

Thanks once again!

To HaHoBe & MarvinP: Thanks for your replies. They turned up in rapid succession just after I'd already used Ron's.

Thanks again to all.

**Ron Coderre** · 01-13-2013, 12:28 PM

I'm glad I could help (again).

**rcsibley** · 05-14-2017, 12:26 AM

I was having the same problem. I put it in some hidden cells on the primary sheet that the user can't access when the sheet is protected - worked like a charm!