Set Default Paste Options in Excel 2007

b88878878887

New Member
Joined
Feb 2, 2010
Messages
1
How do I set my workbook/worksheet to default to "Match Destination Formatting" when user pastes into it?
 

Some videos you may like

Excel Facts

Create a chart in one keystroke
Select the data and press Alt+F1 to insert a default chart. You can change the default chart to any chart type

Mister H

Well-known Member
Joined
Mar 6, 2002
Messages
1,507
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.

Code:
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
 
Last edited:

J J

New Member
Joined
Mar 25, 2012
Messages
2
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,122,709
Messages
5,597,690
Members
414,164
Latest member
ARTW

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top