NOT taking cell borders with copy/paste.

[Ran Taro]

I want to make it so that when a user copies and pastes information from one cell to another, the cell borders don't come along with the information.

I tried using conditional formatting in the target cell to change it back to what I want, but for some reason I cant get the conditional formatter to give me a thick border, which I need in the target cell.

help?!?

 

[malcom]

use paste special...
u myt be looking for VALUES...

 

[Ran Taro]

Thank you, that is a good solution, but not the one I'm after.

There are multiple people who will cut and paste in this spreadsheet, and it will be impossible to get them all to paste special instead of just pasting.

If there is a way to make a conditional format use a thick line for a border, or even a macro that does the same when text is pasted into a cell, that would do the trick...

 

[jindon]

Hi
does this help?

Sub test()
Dim a, rng1 As Range, rng2 As Range
Set rng1 = Application.InputBox("Select range to copy", Type:=8)
Set rng2 = Application.InputBox("select range to paste", Type:=8)
If rng1 Is Nothing Or rng2 Is Nothing Then Exit Sub
a = rng1.Value
rng2.Item(1).Resize(UBound(a, 1), UBound(a, 2)).Value = a
End Sub

 

[Ran Taro]

Very clever Jindon. Not quite right but it does help because it gives me an idea.

I might just use a macro based on worksheet activate to reset the formatting of the target cell each time the worksheet is opened.

 

[Jaafar Tribak]

Hi,

You could also try the following code which ensures ONLY values are pasted:

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim varPastedValue As Variant
    With Application
        If .CutCopyMode Then
            .ScreenUpdating = False
            .EnableEvents = False
            varPastedValue = Target.Value
            .Undo
            Target.Value = varPastedValue
            .EnableEvents = True
            .ScreenUpdating = True
        End If
    End With
End Sub

Regards.

 

[Ran Taro]

That is the perfect solution. Thank you very much.