title: vba excel - preserve destination cell formatting
body: <p>I am relatively new to vba, so please be gentle :)</p>
  
  <p>I have reviewed various scripts which supposedly preserve the formatting of cells on a spreadsheet when ctrl c/ctrl v or copy &amp; paste is being used. Unfortunately I cannot seem to get any of the variations to work for my intentions. I suppose this might be due to the fact that a lot of the data being copy &amp; pasted is being copied from other programs and pasted into the worksheet(therefore copying and keeping the formatting of the program from which it came). All of the macros I've tried to work with all seem to attempt to preserve formatting when copying between cells/worksheets or workbooks and doesn't address the data format when copying from another program. </p>
  
  <p>I'm looking for an alternate approach. From a logical standpoint, I'm thinking that there should be a way on ctrl v or paste event, to have the copied data stored as a variable, stripped of its formatting and to only paste the raw value. I've tried playing around with pastespecial, but I'm not sure how to force a pastespecial (or replace paste with pastespecial). </p>
  
  <p>Here is some code sample, but it doesn't seem to work for me. I keep getting:</p>
  
  <p>cannot run the macro "C:...Test.xlsm'!MyPaste'.  The macro may not be available in this workbook or all macros may be disabled</p>
  
  <p>Macros are definitely enabled and the code is pasted into [ThisWorkbook(Code)]</p>
  
  <pre><code>Option Explicit
  
  Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
      Dim UndoList As String
  
      Application.ScreenUpdating = False
      Application.EnableEvents = False
  
      On Error GoTo Whoa
  
      '~~&gt; Get the undo List to capture the last action performed by user
      UndoList = Application.CommandBars("Standard").Controls("&amp;Undo").List(1)
  
      '~~&gt; Check if the last action was not a paste nor an autofill
      If Left(UndoList, 5) &lt;&gt; "Paste" And UndoList &lt;&gt; "Auto Fill" Then GoTo LetsContinue
  
      '~~&gt; Undo the paste that the user did but we are not clearing the clipboard
      '~~&gt; so the copied data is still in memory
      Application.Undo
  
      If UndoList = "Auto Fill" Then Selection.Copy
  
      '~~&gt; Do a pastespecial to preserve formats
      On Error Resume Next
      '~~&gt; Handle text data copied from a website
      Target.Select
      ActiveSheet.PasteSpecial Format:="Text", Link:=False, DisplayAsIcon:=False
  
      Target.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
      :=False, Transpose:=False
      On Error GoTo 0
  
      '~~&gt; Retain selection of the pasted data
      Union(Target, Selection).Select
  
  LetsContinue:
      Application.ScreenUpdating = True
      Application.EnableEvents = True
      Exit Sub
  Whoa:
      MsgBox Err.Description
      Resume LetsContinue
  End Sub
  </code></pre>