Application.CutCopyMode = xlCopy Does Not Retain Value

[lnagel]

I have an odd issue where the underlying VBA in my "WorkSheet" Code does not retain the value of a "Copy" after pasting to a cell.

Subsequently - I am unable to utilize the qat "UNDO" shared control as the copy value always reverts to Application.CutCopyMode = False immediately after pasting - thus rendering Qat "Undo" virtually useless (also removes the ability to past to additonal cells after the initial paste)

My attempt to remedy so far consists of adding "Application.CutCopyMode = xlCopy" to my preprocessing in the "WorkSheet"

This does result in the selected copy cell showing the "marching ant border" on the copied cell (which it did not do prior to Application.CutCopyMode = xlCopy command) but still does not result in allowing multiple paste functionality or QAT "Undo" functionality

Hoping that someone has run into this issue before and can point me in the right direction as I seem to be totally clueless ATM - Thanks in advance

The vba worksheet code in question is as follows:

Application.CutCopyMode = xlCopy
'------------------------------------------------------------
'Column "E" Processing
'------------------------------------------------------------
   Application.EnableEvents = True
   Set Changed = Intersect(Target, Range("A:A, C:D, G:H"))
   If Not Changed Is Nothing Then
      Application.EnableEvents = False
      For Each c In Changed
        If LCase(Range("E" & c.Row)) = "need" Then
        c.ClearContents
         With Range("A" & c.Row)
            .Value = Range(.Validation.Formula1)(1).Value
         End With
        End If
      Next c
   Application.EnableEvents = True
   Application.CutCopyMode = xlCopy
   End If
'---------------------------------------------------------------
  Application.EnableEvents = True
  Set Changed = Intersect(Target, Columns("E"))
  If Not Changed Is Nothing Then
      Application.EnableEvents = False
      For Each c In Changed
        c.Value = UCase(c.Value)
        If LCase(c.Value) = "need" Then
          Intersect(c.EntireRow, Range("A:A, C:D, G:H")).ClearContents
          With Range("A" & c.Row)
            .Value = Range(.Validation.Formula1)(1).Value
          End With
        End If
      Next c
      Application.EnableEvents = True
      Application.CutCopyMode = xlCopy
  End If
'------------------------------------------------------------

 

[Fluff]

As you code is not copying anything, I don't understand what you mean by

the underlying VBA in my "WorkSheet" Code does not retain the value of a "Copy" after pasting to a cell.

Also when a macro makes any changes it "wipes" the "Undo" stack.

 

[lnagel]

The macro shown applies to the cell (Column E, Row is variable) that I am copying (manually) - I feel like the problem lies in that code and I believe you have probably shed some light

Whan a macro makes any changes it wipes the undo stack - Is there any way to prevent that wipe of the undo stack with VBA Code?

 

[Fluff]

Not that I'm aware of.

 

[lnagel]

Looks like it is "possible" BUT not easily accomplished - Will involve writing code to reverse the macro functionality that wiped the stack. Might play with that a bit. At least I now understand the problem - Thanks so much for your help!

 

[lnagel]

Thanks to Fluff for pointing me in the right direction

In case anyone else has the same question :

The solution is somewhat extensive but looks doable

https://www.jkp-ads.com/Articles/UndoWithVBA00.asp