Free Microsoft Excel 2013 Quick Reference

Match Destination Formatting On Paste

RE: Paste Options defaults

Someone asked this question before, but no one came up with the answer.

Is there a way to set the Paste options in Excel to default to "Match Destination Formatting"? Currently the program defaults to "Keep Source Formatting".

It would be extremely helpful for the work I am doing to have the default be "Match Destination Formatting" instead. I thought there might be a way to set this under "Tools, Options, Edit," but there is nothing.

I am using Excel 2003


Post your answer or comment

comments powered by Disqus
I've recently been working a great deal in MS Excel and have been doing a lot
of moving data from another software tool and between workbook sheets.
Because of purposeful color coding of data in Excel, the new data being moved
is formatted incorrectly. I have the option of using the drop down menu on
the paste clipboard and clicking on "match destination formatting" or waiting
and using the format painter when the data is complete. It would be SO much
simpler if there were a way to set the default option in the paste clipboard
to "match destination formatting" instead of "keep source formatting" but I
have not found any option allowing me to do that! PLEASE help me! This is
making an already tedious process even longer!

Hi,
Is it possible to set the default paste option to "match destination
formatting" so I don't have to go through paste options each time?

Same with using autofill - can I set the default to "fill without formatting"?

Also, occasionally the paste options don't come up properly - after I paste
something the paste options icon pops up but the arrow next to it isn't there
and I can't click on it to access the paste options. This only happens
occasionally and seems to usually happen when the pasted section is next to
the edge of the screen.

Thanks,
Amy

Hi,
Is it possible to set the default paste option to "match destination
formatting" so I don't have to go through paste options each time?

Same with using autofill - can I set the default to "fill without formatting"?

Also, occasionally the paste options don't come up properly - after I paste
something the paste options icon pops up but the arrow next to it isn't there
and I can't click on it to access the paste options. This only happens
occasionally and seems to usually happen when the pasted section is next to
the edge of the screen.

Thanks,
Amy

When I use Control V to paste something there is a little clip board that shows up in the lower right corner. If I click on it, then I can select "Match Destination Formatting." Is there a keystroke that accesses the little clip board? Do I have to use the mouse to access it? Any help would be greatly appreciated since I would rather use the keyboard than the mouse.

Could anybody give me the macro code line to allow me to paste text in the
"match destination format"

In Word it looks like this:

Selection.PasteAndFormat (wdFormatPlainText)

but I can't find anything in Excel.

Thank's a lot in advance!

I am in need of this information also. Can anyone help??

"Dagger St. C." wrote:

> Does anyone know how to Make "Match Destination Format"
> the default setting for pasting into Excel 2003? I know I
> can click the Paste Options button and select it there on
> a per-paste basis, but I'd really like to set this as the
> default.
>

Could anybody give me the macro code line to allow me to paste text in the
"match destination format"

In Word it looks like this:

Selection.PasteAndFormat (wdFormatPlainText)

but I can't find anything in Excel.

Thank's a lot in advance!

How do I make "match destination formatting" automatic when I cut and paste
things into excel??

I have a spreadsheet where I would like everything I paste into to it to
match the formattting of the cells around it--not retain the formatting it
had in its original location. I don't want to have to select "match
destination formatting" every time--I want it to default to that. Any ideas?
Thanks.

I have a spreadsheet where I would like everything I paste into to it to
match the formattting of the cells around it--not retain the formatting it
had in its original location. I don't want to have to select "match
destination formatting" every time--I want it to default to that. Any ideas?
Thanks.

Excel 2003 has the function to match destination formatting when pasting in
to a sheet.
How can I do this in 2000?

Does anyone know how to set the default paste option in Excel so that that
default is "Mathc Destination Formatting"? Quite often I copy data from one
document to another. I want the pasted information to match the format of
the destination document. Is there anyway so set a default so that I can
skip the step of having to specify match?
Thanks.
John H

Does anyone know how to set the default paste option in Excel so that that
default is "Mathc Destination Formatting"? Quite often I copy data from one
document to another. I want the pasted information to match the format of
the destination document. Is there anyway so set a default so that I can
skip the step of having to specify match?
Thanks.
John H

Hi All

I have some cells which have been filled with a certain color. When I try and copy and paste these cells into another sheet I do not get the option to Match Destination Formatting meaning I would have to change many cells colors to no fill every time I paste.

There are other knock on effects from this option being missing including screwing a VLOOKUP which looks through this list. Is there anyway I can get it to display that option when pasting?

Even doing ALT , H , V , M does not work. Its as if the option was never programmed in. I also tried a fresh blank workbook and got the same issue.

Is there even any way to add this in VB and assign a shortcut so instead of Ctrl + V, It pastes using match destenation formatting using say Ctrl + L

There is not such a switch. However, you can create a macro to accomplish
this.

From Excel, hit ALT + F11. This will open the VB Editor. In the editor go
to the Insert menu and select Module. Put the following code into the new
module:

Sub PlainPaste()
ActiveCell.PasteSpecial xlPasteAll
End Sub

Close the VB Editor and go to Tools --> Macro --> Macros. In the Macros
dialog box, make sure the PlainPaste macro is highlighted and hit the
"Options" button on the right. You can specify the hot key (I have mine set
to CTRL + e) here. Hit OK, then close the Macros dialog.

Last step, go to Tools --> Options and click on the Security tab. Click on
the "Macro Security" button at the bottom and set your security to "Medium".
Hit OK, and OK, and you're done!

From then on, whenever you are in that particular Excel document, hitting
CTRL + E (or whatever your hotkey is) will paste the contents of the
clipboard...MINUS formatting information.

Note that this will ONLY affect text. In addition, if you are copying FROM
another Excel worksheet, this macro will only work if you are copying one
cell at a time.

"Frank Kabel" wrote:

> Hi
> AFAIK there's no such switch :-)
>
> --
> Regards
> Frank Kabel
> Frankfurt, Germany
>
> "russweb" > schrieb im Newsbeitrag
> news > Thanks Frank
> >
> > Unfortunately I am pasting alot of different items via the clipboard.
> > Perhaps there is a way of switching 'match destination formatting' on
> as the
> > default?
> >
> > Russell
> >
> > "Frank Kabel" wrote:
> >
> > > Hi
> > > have you tried using 'Edit - Paste Special - Text only'
> > >
> > > --
> > > Regards
> > > Frank Kabel
> > > Frankfurt, Germany
> > >
> > > "russweb" > schrieb im
> Newsbeitrag
> > > ...
> > > > When I paste from word, I lose left border of cells into which I
> > > paste. As
> > > > this is invoice, causes alot of time, then reformatting cells.
> > > >
> > > > All help appreciated
> > >
> > >
>
>

Hi all,

I have made a spreadsheet that has been designed so that user can either enter their data from scratch or copy and paste it in from other spreadsheets. Using this newer version of Excel (2002) when I paste something into the spreadsheet I get two options: 'Keep Source Formatting' and 'Match Destination Formatting'.

Is there any way to set the 'Match Destination Formatting' option as the default option, or better yet force the Paste function to always use this option?

Many Thanks,

~T.

Is it possible to set a group of cells to by default match the destination formatting?

I want to set the default to "Match destination Formatting" when pasting into Excel. I've searched through the help and can not find anything for this. Has anyone else figured this out?

Is this possible? To not have to click the little icon then match dest format?

I have a spreadsheet that I updated and changed a lot of the formatting. The people who use it want to copy their old information over (it's a lot) and it needs to take the formatting of the destination cells in the new workbook. They don't know how/don't want to use "paste special" for everything, because it would take a lot of time. Is there a way to have it automatically take the format of the destination cells on a regular Copy & Paste?

Hi,

I'm trying to copy paste special a format on a range of column (value in number) but it is not working.

Here is my code. Can you please help me making it work.


	VB:
	
 usedrng = ActiveSheet.UsedRange 
FirstRow = usedrng(1).Row 
FirstCol = usedrng(1).Column 
lastrow = usedrng(usedrng.Cells.Count).Row 
lastcol = usedrng(usedrng.Cells.Count).Column 
 
Columns("B:B").Select 
Selection.Copy 
Selection.PasteSpecial Paste:=xlPasteFormats, Operation:=xlNone, _ 
SkipBlanks:=False, Transpose:=False 
Application.CutCopyMode = False 
Range(Columns(lastcol + 2), Columns(lastcol + 6)).Select 
Selection.PasteSpecial Paste:=xlPasteFormats, Operation:=xlNone, _ 
SkipBlanks:=False, Transpose:=False 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
Thank you for your help

Book1.xlsm
I have a main workbook with a Tails sheet and an Assays sheet. The tails sheet has a list of sample IDís in column A. The Assays sheet is blank and every day I open up a different worksheet and copy and paste the data from that sheet into the Assays sheet. The copied data is a list with sample IDís in column A and then assays in columns B to AL. The data is copied in every day and the sample IDís are different every time
I need to write a macro to find if any of the copied sample IDís in the Assays sheet match the sample IDís in the Tails sheet and when they do copy and paste from corresponding data in that row (from columns B to AL) into the matching row in the Tails sheet.
I tried to use a for next loop with a match function but once it has found the matches it continues to paste the last match into the rest of the list and in my larger workbook it is very slow.
I have attached a copy of the worksheet

	VB:
	
[FONT=Calibri][SIZE=3]Sub FindPaste()[/SIZE][/FONT] 
[FONT=Calibri][SIZE=3]Dim r As Long 
Dim LastRowTails As Long 
Dim LastRowAssays As Long 
Dim strLookup As String[/SIZE][/FONT] 
[FONT=Calibri][SIZE=3]LastRowTails = Worksheets("Tails").Cells(65536, 1).End(xlUp).Row[/SIZE][/FONT] 
[FONT=Calibri][SIZE=3]LastRowAssays = Worksheets("Assays").Cells(65536, 1).End(xlUp).Row[/SIZE][/FONT] 
[FONT=Calibri][SIZE=3]Worksheets("Assays").Select[/SIZE][/FONT] 
[FONT=Calibri][SIZE=3]Set rngLookup = Range("A1", Cells(LastRowAssays, 1))[/SIZE][/FONT] 
[FONT=Calibri][SIZE=3] For i = 2 To LastRowTails 
On Error Resume Next 
strLookup = Worksheets("Tails").Cells(i, 1) 
r = Application.WorksheetFunction.Match(strLookup, rngLookup, 0) 
With Worksheets("Assays") 
    .Range(.Cells(r, 2), .Cells(r, 38)).Copy Worksheets("Tails").Cells(i, 2) 
End With 
Next i[/SIZE][/FONT] 
[FONT=Calibri][SIZE=3]Worksheets("Tails").Select[/SIZE][/FONT] 
[FONT=Calibri][SIZE=3]End Sub[/SIZE][/FONT] 
[FONT=Calibri][SIZE=3][/SIZE][/FONT] 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines


I'm having an issue with copying and pasting as well as draggin a formula
down a column. I have the rows set up to alternate colors for visual effect.
The issue I have is when copying and pasting it will undo a cells format
(changing to whatever color [and border etc] was in the copied cell.) I know
you can fix this by clicking the automatically inserted drop down box and
selecting "match destination formatting." The ammount of copying/pasting I
do with this spreadsheet would make that option take forever.

Is there a way to make the "match destination formatting" default?

I need some help. I've copied 100's of names and addresses from google spreadsheets to excel. They don't copy over very well, there are a lot of 'hidden' bits. The names and addresses are all over the place, some you have to click on the cell to retrieve the info as a new line has been created.
So... I used paste special, then match destination formatting. This did the trick but left small squares at the end of each row (where I guess the new line/paragraph would have been...) so how do I get rid of the squares?

Secondly, the names and address look like this each line of the address is in a seperate cell:

Bruce Butler
17 The Cottage
Anywhere
HA4 7TT

I need them to read across the page like this, with each line in a new cell:

Bruce Butler 17 The Cottage Anywhere HA4 7TT

I know you can 'transpose' but that only works individually, how do I transpose the whole sheet, I have many sheets with hundereds of names and addresses.

Phew I'll be a very happy girl when this is sorted, thanks in advance for your help.


No luck finding an answer? You could always try Google.