vba excel - preserve destination cell formatting

Question

I am relatively new to vba, so please be gentle :)

I have reviewed various scripts which supposedly preserve the formatting of cells on a spreadsheet when ctrl c/ctrl v or copy & 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 & 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.

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).

Here is some code sample, but it doesn't seem to work for me. I keep getting:

cannot run the macro "C:...Test.xlsm'!MyPaste'. The macro may not be available in this workbook or all macros may be disabled

Macros are definitely enabled and the code is pasted into [ThisWorkbook(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

    '~~> Get the undo List to capture the last action performed by user
    UndoList = Application.CommandBars("Standard").Controls("&Undo").List(1)

    '~~> Check if the last action was not a paste nor an autofill
    If Left(UndoList, 5) <> "Paste" And UndoList <> "Auto Fill" Then GoTo LetsContinue

    '~~> Undo the paste that the user did but we are not clearing the clipboard
    '~~> so the copied data is still in memory
    Application.Undo

    If UndoList = "Auto Fill" Then Selection.Copy

    '~~> Do a pastespecial to preserve formats
    On Error Resume Next
    '~~> 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

    '~~> 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

Answer 1

The reason for the error message is that your code is an event handler

see:

• MSDN Office Online Reference: Creating VBA Macros to Manipulate Worksheets in Excel 2007
• MSDN Office Online Reference: Running VBA Code When Events Occur in Excel 2010

and

• Chip Pearson MVP and all round Excel Chieftan: Events And Event Procedures In VBA
• Ozgrid MVP and all round Excel VBA Top Banana: Worksheet Events In Excel VBA

basically the Worksheet.Change Event (Excel) is fired when the user changes a cell in the worksheet. Excel passes in the Worksheet Object object as sh and the Range Object (Excel) as Target. Your code then uses these objects (Ozgrid Excel VBA Crash Course Lesson 4 - Common objects).

as David Zemens has suggested, you need to use the PasteSpecial method of the Sheet object. for further info, see MSDN libray: PasteSpecial Method [Excel 2003 VBA Language Reference].

and when you have finished all that reading, you will be ready to copy paste my code below:

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

    '~~> Get the undo List to capture the last action performed by user
    UndoList = Application.CommandBars("Standard").Controls("&Undo").List(1)

    '~~> Check if the last action was not a paste nor an autofill
    If Left(UndoList, 5) <> "Paste" And UndoList <> "Auto Fill" Then GoTo LetsContinue

    '~~> Undo the paste that the user did but we are not clearing the clipboard
    '~~> so the copied data is still in memory
    Application.Undo

    If UndoList = "Auto Fill" Then Selection.Copy

    '~~> Do a pastespecial to preserve formats
    On Error Resume Next
    '~~> Handle text data copied from a website
    Target.PasteSpecial Paste:=xlPasteValues
    On Error GoTo 0

    '~~> Retain selection of the pasted data
    Target.Select

LetsContinue:
    Application.ScreenUpdating = True
    Application.EnableEvents = True
    Exit Sub
Whoa:
    MsgBox Err.Description
    Resume LetsContinue
End Sub

so, bada bing bada bing, you have your working code, and some reading that should help you to understand better what your code is doing and how it does it.