CODEWITHSUNDEEP
excelvbacopy-paste
Sassbearilla
Sassbearilla

Reputation: 31

Combine Two Types of Paste in an Excel VBA Macro

My wish is to set the default paste in Excel as values only or match destination formatting. I know how to create a macro for each one individually, but xlPasteValues only works when you copy from a cell in a workbook, and match destination formatting doesn't (but it does work when copying from a webpage, which is what I want). My goal is to create a single VBA macro that combines both so I only have to use one command and regardless of whether I copied from a cell or webpage, it will paste without any source formatting.

Pseudo code:

Sub SuperPaste()
     if clipboard source is a cell (or vice versa, whatever is easier):
          Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
               :=False, Transpose:=False
     else:
          ActiveSheet.PasteSpecial Format:="HTML", Link:=False, DisplayAsIcon:= _
               False, NoHTMLFormatting:=True
(maybe some error handling)
End Sub

Upvotes: 0

Views: 688

Answers (1)

Spinner
Spinner

Reputation: 1088

The following does what you're attempting to do:

Sub SuperPaste()
     
''' Clipboard source is a current-instance excel cut:
''' - only option is to paste all (or throw an error msg)
    If Application.CutCopyMode = xlCut Then
        ActiveSheet.Paste
        
''' Clipboard source is a current-instance excel copy:
''' - paste values only (keeps destination formats)
''' - am pasting to the activecell to avoid mis-matched source and destination selections
    ElseIf Application.CutCopyMode = xlCopy Then
        ActiveWindow.ActiveCell.PasteSpecial xlPasteValues
            
''' Clipboard is empty: report to user
    ElseIf Application.ClipboardFormats(1) = -1 Then
        MsgBox "Nothing has been copied."

''' Clipboard source is another application (including another instance of excel):
''' - paste text (same as paste values for excel)
''' - still retains numbers (if tabbed or tabled apart) from word, html, another instance of excel, etc.
    Else: ActiveSheet.PasteSpecial Format:="Text"
    End If

End Sub

Notes:

  1. You can do more sophisticated things by declaring Lib functions for working with the clipboard
  2. However, the above works for 99.9% of the types of copies where you want to keep destination formats

EDIT: Added handling of an empty Clipboard

Upvotes: 2

Related Questions