Set Default Paste Options in Excel 2007

[b88878878887]

How do I set my workbook/worksheet to default to "Match Destination Formatting" when user pastes into it?

 

[Mister H]

Hi:

WELCOME to the Board.

If you paste the code below in the ThisWorkbook (in the Visual Basic Editor) your Paste is automatically reversed and changed to a Paste Special which will maintain all formatting in the cell that the stuff is being Pasted into.

If you are not familiar with the VBA Module then do this.

Copy the code below and then RIGHT Click the Excel symbol to the LEFT of FILE and Paste it in ThisWorkbook

Hopefully this solves your problem.

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
' this code will undo PASTE and instead do a PASTE SPECIAL VALUES which will
' allow you to retain FORMATS in all of the cells in all of the sheets, but will
' also allow the user to COPY and PASTE data
    Dim UndoString As String
    Dim srce As Range
    On Error GoTo err_handler
    UndoString = Application.CommandBars("Standard").Controls("&Undo").List(1)
 
    If Left(UndoString, 5) <> "Paste" And UndoString <> "Auto Fill" Then
 
        Exit Sub
 
    End If
 
    Application.ScreenUpdating = False
    Application.EnableEvents = False
    Application.Undo
 
 
    If UndoString = "Auto Fill" Then
 
        Set srce = Selection
 
        srce.Copy
 
        Target.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
                    :=False, Transpose:=False
 
        Application.SendKeys "{ESC}"
        Union(Target, srce).Select
 
    Else
 
        Target.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
                    :=False, Transpose:=False
 
    End If
 
    Application.ScreenUpdating = True
    Application.EnableEvents = True
    Exit Sub
err_handler:
    Application.ScreenUpdating = True
    Application.EnableEvents = True
End Sub

Good Luck,
Mark

P.S. Here is a link to my orignal Post regarding the same type of issue:

http://www.mrexcel.com/forum/showthread.php?t=378163&highlight=paste+special

 

[J J]

I ahave been experiencing problems with the paste special functionality in excel.

When I attempt to copy a cell or range of cells within the same worksheet, I am only getting the paste special window with "formatted text" and "Text" options, (not the window with formats, formulas, values, etc).

This has only happened recently, so I can only assume that I have inadvertently changed something within excel (excel options maybe), but I have been unable to identify the cause of this problem.

I hope you or someone else can help me here.