May 21, 2004

Take What You Need

Copying and Pasting. These two features sure save a lot of time and can be found in all kinds of software (Shortcut keys are invariably Ctrl + C to copy and Ctrl + V to paste, use them for text, images and files). But there are special variants of Pasting that can make working with Excel even faster and easier.

The Paste Special Dialog box
The easiest way to open this dialog box is to right click where you want to paste (you can't use it unless there is something already copied to the clipboard). Alternatively you could try Alt, E and S if you prefer shortcut keys.

Apart from the All option which pastes everything, the other useful options will probably be Formulas, Values, Formats and Comments. Use them accordingly, they do what they say. (Note that for formulas used in Conditional Formatting, the Formats option should be used instead of the Functions option)

Paste Special Buttons
Another way to paste just what you want to is to use the Paste Formats and Paste Values buttons. Open the Customize dialog box in the Tools Menu and select Edit from the Commands tab. Scroll down till you find the buttons and drag them across to a toolbar of your choice.

Hmm, but what about Paste Formulas and Past Comments? For some reason these buttons are not available, but no problem! You can download my macros called PasteFormulas and PasteComments and import them via your Visual Basic Editor. (I recommend putting them in your Personal Workbook)

Open up your Visual Basic Editor (Alt + F11) and go the File menu and then select Import File. (Make sure you click where you want to import them first. For example, pick the Modules folder in your Personal Workbook if you want to import there)

Now they are ready to use but you still need a way to activate them easily. Go back to the Customize dialog box in the Tools menu and scroll down on the Commands tab until you find Macros and then drag a Custom Button to a toolbar. (For editing tips see my Customizing Toolbars tip dated May 2) Right click the button and choose Assign Macro to open the selection of the Macros available in the dialog box.

There you have it. Note that the PasteFormulas and PasteComments work the same way as the regular Paste Formats and Paste Values buttons, you can use them to paste indefinitely while something is copied on the Clipboard.

Here's a picture of my own customized PasteFunctions Custom button.

Hope they come in handy :)

Custom button is very handy. I tend to use it via macro. ;-) Colo
Post a Comment

This page is powered by Blogger. Isn't yours?