What it does
Uses picker prompt to help expedite paste special tasks (values, formulas, formats, etc.), including some custom macro options.  Most options include the ability to transpose.  It also includes the ability to paste up to five customizable values.  See the full list of options below in the Instructions section.

When to use it
When working with data or files, and you want to paste something other than cells (formulas, formats, raw text, etc.), this is an easy way to quickly apply paste actions.  It works well in conjunction with standard copy functions as well as the Copy Special Picker.

Why to use it
It’s easier and quicker than using the mouse, combining several clicks into one step, providing shortcuts to tougher-to-find menu options, and providing options that extend the native capabilities of Excel.

Default shortcut
Ctrl+Shift+V

Other Details

• Category: Copy and Paste / Paste
• Difficulty: 2/5
• Usage/frequency: 5/5
• Automation factor: 3/5 (estimated 3 seconds saved each time used)
• Type: Shortcut
• Date added: 11/11/2017
• Tags: paste, picker

Related Macros
[V] Paste Values Number Formats
[F] Paste Formulas
[T] Paste Text
[U] Paste Uniques
[LT] Paste Links Transpose
[E] Paste Non Blanks
[P] Apply Format Default
[Z] Paste Sheets
[M] Paste Delimited Text

Note: most of the related filter macros above will be covered in the instructions below.  The picker option code is displayed in the [brackets].

Other Articles
None

None

Prerequisites
Most options: copy cell(s), then select range of cell(s) to paste to

Instructions
With cells/sheets copied per the prerequisites above, run the Paste Special Picker macro.  It will present several helpful actions to run for pasting data other than just cells with formatting.  For the action you run most often, you can set your default so you don’t have to enter it (just launch the picker and press Enter).  Note that the default is highlighted when the picker is launched, so to change the action code, there is no need to press the backspace first.  Further instructions for the individual action codes are described below.

Note that for most options below, adding a T to the code in brackets will transpose the copied cells.

• [A] This will paste with all components, just like the default Excel copy and paste functions.
• [V] This will paste values only, for all selected visible cells.  This is intentional so that if you are using a filter, you don’t accidentally paste over the non-visible cells.
• [F] This will paste formulas only, for all selected visible cells. This will paste formulas based on whether they have absolute or relative references.
• [B] This will paste formulas only, but the exact formulas will be copied (similar to absolute references), regardless of whether any cell references in the formula are relative or absolute.
• [T] This will paste raw, formatted text from the clipboard, regardless of what application it was copied from.
• [S] This will paste formats only, similar to the format painter.
• [N] This will paste number formats and formulas only – not other formats such as borders, fonts, etc.
• [C] This will paste cell comments.  If any of the copied cell(s) have no comments, no comments are pasted for the related cells.
• [U] This will paste values and remove duplicates (leaving unique values).  Optionally, you can add an A to sort the unique values in ascending order. 
• [L] This will paste links to the copied cells, so that if the copied cell values change, the values in the pasted range also change.
• [H] This will paste the clipboard contents to a hyperlink in the selected cell(s).
• [D] This will paste data validation to the selected cell(s).
• [W] This will paste column widths to the selected cell(s).
• [E] This will paste non-blank cells, first pasting the copied cell(s), then deleting any blank cells (shifting the blank cells up).
• [P] This will apply formats used in cell(s) specified with the Copy Special Picker – set format range option [A].
• [Z] This will paste sheets that were specified with the Copy Special Picker – set copy sheets option [Z].
• [M] This will paste any delimited text in the clipboard across multiple cells.
• [Q] This will launch the paste special dialog box.

Like most of the picker macros, you can set the default option to apply so you don’t even have to enter the letter/number – just the Enter key.  Just enter your preferred default letter(s), then click the default checkbox, and those letter(s) becomes the new default.

You can also set up to five custom values or formulas you can paste to the selected cell(s).  To set a custom value, enter option [X], then a space, then the formula or value, then a space, then the word DEFAULT, then the number you want to assign the custom value/formula to (1/2/3/4/5).  For example, enter “X NOTE: DEFAULT1” to set a custom value of “NOTE:” that can be easily pasted by just entering a 1 and clicking the Enter key when you run the Paste Special Picker macro.