Go Back   TechArena Community > Software > Windows Software
Become a Member!
Forgot your username/password?
Tags Active Topics RSS Search Mark Forums Read

Sponsored Links



Match Destination Formatting as default in Excel 2007

Windows Software


Reply
 
Thread Tools Search this Thread
  #1  
Old 27-11-2010
Member
 
Join Date: Oct 2010
Posts: 61
Match Destination Formatting as default in Excel 2007
  

I am using MS Office 2007 on my desktop on which I have installed the Windows XP. Actually there is no issue with the Office 2007 but I have found something which is very much annoying for me. When I paste data into an Excel spreadsheet from any external source such as email, web page, document, etc then that data gets pasted with the "Keep Source Formatting" option followed by the POP UP which let me select for the "Match Destination Formatting". I want to do some settings which makes it default so that I don't have to select it again and again. Can anybody help?

Reply With Quote
  #2  
Old 27-11-2010
Member
 
Join Date: Nov 2008
Posts: 1,259
Re: Match Destination Formatting as default in Excel 2007

I guess you need to change the default behavior of the MS Excel while pasting any data on the excel sheet. particularly you have to change the behavior of the Ctrl+V which would be the perfect one. You have to use the Macros for doing this. I have given the following code which you have to add in the Personal.xlsb workbook.
  • In the workbook code pane;
Code:
Sub Auto_Open()
  Application.OnKey "^v", "PasteValues"
End Sub

Sub Auto_Close()
  Application.OnKey "^v"
End Sub
  • In a module code pane;
Code:
Public Sub PasteValues()

' Keyboard Shortcut: Ctrl+v

  ActiveSheet.PasteSpecial Format:="Unicode Text"
End Sub
Reply With Quote
  #3  
Old 27-11-2010
Member
 
Join Date: May 2008
Posts: 979
Re: Match Destination Formatting as default in Excel 2007

Recording a macro is to perform the following actions to be recorded. The procedure is as follows:
  1. Activate the menu Tools-Macro-Record New Macro.
  2. It names the future macro: Go
  3. We can also assign a function key that will activate the macro: Option + Command + G
  4. Confirm dialog
  5. Therefore any user action is stored in the macro.
  6. This is the toolbar that allows Visual Basic to stop the recording mode. It appears automatically.
* Sequence of actions to save
  • Select the range containing the user's responses (C6)
  • erase the contents of the selection
  • enter "OK" in the cell L2C6
  • validate

* Stop Recording
  • Click the stop button of the Visual Basic toolbar
  • The Visual Basic toolbar disappears
  • The macro is now saved and can be used
Reply With Quote
  #4  
Old 27-11-2010
Member
 
Join Date: Mar 2009
Posts: 1,360
Re: Match Destination Formatting as default in Excel 2007

The Excel macro recorder records all your actions on the keyboard or mouse in a macro. Run it, perform the task to automate, then stop it. Before you start recording a macro, you'd better make sure that all stages of the task you are familiar. A good practice is to write them down on a sheet of paper, or to make a first "blank" to avoid any surprises. The macro recorder records all your actions, including typos or errors. Record only what is necessary and without error requires good knowledge of the various steps in advance: it is necessary to focus, however, the parameters related to a macro, and especially the concept of macro absolute or relative.
Reply With Quote
  #5  
Old 27-11-2010
Member
 
Join Date: Nov 2008
Posts: 1,514
Re: Match Destination Formatting as default in Excel 2007

  • If you select an area or specific cell after starting the recording, the macro will run well and apply your instructions to or from the cell or range states, without changes possible: it is an absolute macro. Do this when you insist that the macro assigns the same cell, like placing a date in cell A1.
  • If you select the cell or range before running the macro, the selection process will not be recorded in the macro and it will run on or from the active zone, whatever: it is a macro file. Macros are much more versatile relative. Excel lets you create macros entirely on: if you select a track during recording, it is referenced relative to, the cell initially active, contrary to what happens in a macro absolute (default).
To save the macro relation, click the Use relative references in the Code group of the Developer tab, then start recording.
Reply With Quote
  #6  
Old 27-11-2010
Member
 
Join Date: Nov 2008
Posts: 996
Re: Match Destination Formatting as default in Excel 2007

To record a macro, follow these steps:
1.In the group Code Developer tab, click Record New Macro. This opens the Record Macro dialog.
2.Enter a name in the Macro Name box. A macro name can contain letters, digits, underscores, but not space. It must begin with a letter and its length should not exceed 64 characters. Prefer short names and evocative.
3.Select the storage location for the macro in the combobox Record Macro (by default, a new macro is stored in the current document).
Reply With Quote
  #7  
Old 27-11-2010
Member
 
Join Date: Nov 2008
Posts: 1,185
Re: Match Destination Formatting as default in Excel 2007

1. The Personal Macro Workbook in Excel is a file named Personal.xlsb. Windows Vista, this workbook is saved in the folder:
  • C: \ Users \ username \ AppData \ Local \ Microsoft \ Excel \ Xlstart
2. Microsoft Windows XP, it is located in the folder:
  • C: \ Documents and Settings \ username \ Application Data \ Microsoft \ Excel \ Xlstart
3. Workbooks in the folder XLStart open automatically when you start Excel.
4. Enter possibly in the Description box a brief description of the action carried out through the macro.
5. You can also assign a keyboard shortcut to the macro, entering the letter to be used in the Shortcut Key box. A keyboard shortcut macro takes precedence over those of the program. Generally, most of the key combinations Ctrl + letter are already affected. Better hold Ctrl + Shift + letter to avoid confusion.
6. Click OK to close this dialog box.
7. In the status bar button A macro is being recorded appears. Click here to stop recording.
8. Make chronologically each of the tasks must be recorded. Once the tasks performed, to stop recording, click Stop Recording or Stop Recording in the Code group of the Developer tab.
Reply With Quote
  #8  
Old 29-11-2010
Member
 
Join Date: Mar 2009
Posts: 1,360
Re: Match Destination Formatting as default in Excel 2007

It is easy to forget that a recording is in progress. One continuing until explicitly stopped, it is critical that you stop recording when the tasks are completed to register. If you forgot to stop recording a macro in time, you do not have to record everything again: just edit the macro and remove unnecessary actions.
Reply With Quote
Reply

  TechArena Community > Software > Windows Software
Tags: , , , , ,



Thread Tools Search this Thread
Search this Thread:

Advanced Search


Similar Threads for: "Match Destination Formatting as default in Excel 2007"
Thread Thread Starter Forum Replies Last Post
How to set a default location to run Excel 2007 Nyota MS Office Support 2 23-02-2012 01:21 PM
Microsoft Excel 2007 formatting Meditation Windows Software 4 29-01-2011 04:58 PM
How to create formatting styles in Excel 2007 jEEMOOT Windows Software 4 29-01-2011 03:41 PM
Writing a conditional formatting with Excel 2007 KAMAL60 Software Development 5 23-10-2009 06:19 PM
Set paste option to always match destination format Ayush Windows Software 5 07-07-2009 06:23 PM


All times are GMT +5.5. The time now is 02:18 AM.