Excel Add-in Paste Special a complete reference with code example

PasteSpecial

Method determines paste copied information from clipboard to the destination in a specific format. The default paste copies everything (formatting, formula, value etc.). Excel offers Paste Special method which provides various options as listed below:

Paste options

1. All: copies everything including formatting, value, formulas, comments and validations.
2. Formulas: only formulas will be copied from source to destination
3. Values: copies only values from source to destination
4. Formats: only formatting would be copied from source to destination
5. Comments: only comments would be copied from source to destination
6. Validations: only validations would be copied from source to destination
7. All using source theme: theme of the copied item will be pasted
8. All except borders: Paste will take place except border
9. Column widths: only column width would be pasted
10. Formula and number formats: only formulas and number formatting would be pasted
11. All merging conditional formats: merged conditional formats will be pasted

Paste operations

Can be clubbed with Paste methods listed above to enhance paste operation like you can Multiply, Add, Subtract or Divide on copied data.

1. None: its default, no operation would be performed
2. Add: all values would be added with copied item
3. Multiply: all values would be multiplied with copied item
4. Subtract: all values would be subtracted with copied item
5. Divide: all values would be divided with copied item
6. Skip blanks: blank cells would be ignored from the copied items
7. Transpose: copied values would be pasted horizontally or vertically

All with Multiply operation

1. Copy any cell having numeric value from which you would like to multiply target values
2. Select target range
3. Press ALT + E + S or click on Paste button drop-down and select Paste Special from Home tab
4. Select Values from Paste block and Select Multiply from Paste Operations block
5. Say OK

Paste Column widths

1. Select Column and Copy
2. Select destination Column
3. Press ALT + E + S or click on Paste button drop-down and select Paste Special from Home tab
4. Select Column widths option from Paste Special dialog
5. Say OK

Copy format

Public Sub PasteSpecialFormattingExample()
    'Worksheet object
    Dim oSheet As Worksheet
    
    'Bind activesheet reference
    Set oSheet = ActiveSheet
    
    'Declare range object
    Dim oSourceRange As Range
    
    'Bind selection to range
    Set oSourceRange = Selection
    
    'Declare target object
    Dim oTargetRange As Range
    
    'Bind target range
    Set oTargetRange = oSheet.Range("D2")
    
    'copy source
    oSourceRange.Copy
    
    'Paste format
    oTargetRange.PasteSpecial xlPasteFormats
    
    'Cleanup
    If Not oSourceRange Is Nothing Then
        Set oSourceRange = Nothing
    End If
    If Not oTargetRange Is Nothing Then
        Set oTargetRange = Nothing
    End If
End Sub

Output

Copy formulas

Public Sub PasteSpecialFormulasExample()
    'Worksheet object
    Dim oSheet As Worksheet
    
    'Bind activesheet reference
    Set oSheet = ActiveSheet
    
    'Declare range object
    Dim oSourceRange As Range
    
    'Bind selection to range
    Set oSourceRange = Selection
    
    'Declare target object
    Dim oTargetRange As Range
    
    'Bind target range
    Set oTargetRange = oSheet.Range("D2")
    
    'copy source
    oSourceRange.Copy
    
    'Paste format
    oTargetRange.PasteSpecial xlPasteFormulas
    
    'Cleanup
    If Not oSourceRange Is Nothing Then
        Set oSourceRange = Nothing
    End If
    If Not oTargetRange Is Nothing Then
        Set oTargetRange = Nothing
    End If
End Sub

Output

Copy with Multiply operations

Public Sub PasteSpecialWithMultiplyExample()
    'Worksheet object
    Dim oSheet As Worksheet
    
    'Bind activesheet reference
    Set oSheet = ActiveSheet
    
    'Declare range object
    Dim oSourceRange As Range
    
    'Bind selection to range
    Set oSourceRange = Selection
    
    'Declare target object
    Dim oTargetRange As Range
    
    'Bind target range
    Set oTargetRange = oSheet.Range("C2:C6")
    
    'copy source
    oSourceRange.Copy
    
    'Paste format
    oTargetRange.PasteSpecial Operation:=xlPasteSpecialOperationMultiply
    
    'Cleanup
    If Not oSourceRange Is Nothing Then
        Set oSourceRange = Nothing
    End If
    If Not oTargetRange Is Nothing Then
        Set oTargetRange = Nothing
    End If
End Sub

Output before

Output after

Transpose

Select the range that you would like to paste as transpose. In this example I will copy rows and paste in each column with the help of transpose:

Steps

1. Selection Range
2. Copy by pressing CTRL + C from keyboard
3. Select single cell of the starting cell where you would like to paste data
4. Press ALT + E + S key from keyboard or Paste special from Home tab
5. Enable Transpose checkbox from paste operations block
6. Say OK

Example

Next >> Understand IF, Nested IF with Logical functions AND, OR, NOT

Share on Facebook

Tweet