Excel macro sometimes works and sometimes doesn't

Question

I have no VBA experience, but was in need of being able to paste into cells without the formatting changing. I found this script in a thread here:

Private Sub Worksheet_Change(ByVal Target As Excel.Range)

    If Application.CutCopyMode = xlCopy Then

        Application.EnableEvents = False

        Application.Undo
        Target.PasteSpecial Paste:=xlPasteValues

        Application.EnableEvents = True

    End If

End Sub

I added this macro to my excel file and I thought it was working at first. However, after further testing it only seems to work half the time and I can't figure out why. I make sure to have macros enabled every time I have tested it. What could cause this to work sometimes and then not work at other times?

When you run a macro generally you cannot undo any operation that said macro completes. So if you have a macro that updates a sheet then you lose any previous 'undos' that existed from before you ran it — Marcucciboy2, Aug 03 '18 at 18:27
Do you have the 'dancing ants' when it fails? You may be interested in [Should I turn .CutCopyMode back on before exiting my sub procedure?](https://stackoverflow.com/questions/33833318) — , Aug 03 '18 at 18:28
@Jeeped, yes. When it fails copying and pasting seems to still work fine, but I lose my formatting. — ayylmao1234, Aug 03 '18 at 18:43
@Marcucciboy2, does that just mean that I can't undo my paste? Not being able to undo isn't a big concern for me. Keeping the formatting is my main concern. — ayylmao1234, Aug 03 '18 at 18:47
Ah it depends. ["This method undoes only the last action taken by the user before running the macro, and it must be the first line in the macro. It cannot be used to undo Visual Basic commands"](https://msdn.microsoft.com/en-us/vba/excel-vba/articles/application-undo-method-excel). The oversight Jeeped is pointing out is sort of independent of this — Marcucciboy2, Aug 03 '18 at 18:51
`PasteSpecial` actually has 4 parameters you can set. Try this: `Target.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False` I'm using these parameters in a keyboard shortcut that works for me. — Rey Juna, Aug 03 '18 at 19:26
@ReyJuna, Thanks! I haven't had this fail since adding those parameters. This probably is better than the accepted answer too. — ayylmao1234, Aug 03 '18 at 19:56
Great! Some VBA parameters have a standard default and some default to the last used. I feel more sure of the outcome when I specify all of them. — Rey Juna, Aug 03 '18 at 21:35

score 1 · Accepted Answer · answered Aug 03 '18 at 19:36

1

Maybe something more like this:

Private Sub Worksheet_Change(ByVal Target As Excel.Range)

    Dim val

    If Application.CutCopyMode = xlCopy Then
        val = Target.Value
        Application.EnableEvents = False
        Application.Undo
        Target.Value = val
        Application.EnableEvents = True
    End If

End Sub

...as long as you don't need to copy/paste formulas

answered Aug 03 '18 at 19:36

Tim Williams

154,628
8
97
125

I don't need those and this seems to work so far, thanks! – ayylmao1234 Aug 03 '18 at 19:51

score 0 · Answer 2 · answered Aug 03 '18 at 20:08

Another option would be to simply take over control of Ctrl-V with Application.OnKey whenever that worksheet is activated. This lets you make the paste behavior pretty much anything you want, assuming that it is accessed via the keyboard shortcut and not the menus.

Private Sub Worksheet_Activate()
    'Take over Ctrl-V on this sheet.
    Application.OnKey "^{v}", "PasteValues"
End Sub

Private Sub Worksheet_Deactivate()
    'All done, cede Ctrl-V back to Excel.
    Application.OnKey "^{v}"
End Sub

Public Sub PasteValues()
    If Not Selection Is Nothing Then
        Selection.PasteSpecial Paste:=xlPasteValues
    End If
End Sub

Excel macro sometimes works and sometimes doesn't

2 Answers2