VBA to prevent worksheet formatting when copying and pasting?

Question

I have a sheet in which the user will be doing lots of copying and pasting of values. I want the user to be able to copy and paste values but without changing the formatting of the sheet. I'm wondering if this can first be achieved with protection settings, and if not, if this can be achieved with VBA?

Many thanks.

Use paste.special, something like this: https://stackoverflow.com/q/50776026/4961700 — Solar Mike, Jun 06 '23 at 13:38

score 2 · Answer 1 · answered Jun 06 '23 at 13:59

The build-in function of Excel would be to ask the user to use PasteSpecial and copy only the values. However, users tend to not follow that.

What you could do is to create a Change routine that saves the copied values into memory, issue an Undo-Command that will revert the last action, including formatting, and then write back the saved values into the cells. Put the code into the worksheet module of that sheet:

Option Explicit

Private Sub Worksheet_Change(ByVal Target As Range)
    Application.EnableEvents = False
    
    ' Save modified value
    Dim v
    ReDim v(1 To Target.Areas.Count)
    Dim i As Long
    For i = 1 To Target.Areas.Count
        v(i) = Target.Areas(i).Value2
    Next i
    ' Undo last action
    Application.Undo
    
    ' Copy only the values of the last modification
    For i = 1 To Target.Areas.Count
        Target.Areas(i).Value2 = v(i)
    Next i
    Application.EnableEvents = True
End Sub

score 0 · Answer 2 · answered Jun 06 '23 at 14:38

0

Copy and paste values in one line of VBA

Range("B1:B3").Select: Selection.Copy: Range("P1").Select: Selection.PasteSpecial Paste:=xlPasteValues: Application.CutCopyMode = False: Range("A1").Select

answered Jun 06 '23 at 14:38

user10186832

423
1
9
17

And how will you trigger that code? – FunThomas Jun 06 '23 at 14:57

VBA to prevent worksheet formatting when copying and pasting?

2 Answers2