Pasting


Range Object - Paste Special

Pastes a Range from the Clipboard into the specified range.

Range("A1").PasteSpecial Paste:=xlPasteType.xlPasteValues, _ 
                         Operation:=xlPasteSpecialOperation.xlPasteSpecialOperationAdd, _
                         SkipBlanks:=False, _
                         Transpose:=False

Paste -
Operation -
SkipBlanks - Whether to have blank cells in the range on the Clipboard not be pasted into the destination range. The default value is False.
Transpose - Whether to transpose rows and columns when the range is pasted. The default value is False.


Worksheet Object - Paste Special

Pastes the contents of the Clipboard onto the sheet, using a specified format. Use this method to paste data from other applications or to paste data in a specific format.
You must select the destination range before you use this method. This method may modify the sheet selection, depending on the contents of the Clipboard.

ActiveSheet.PasteSpecial Format:="Text", _ 
                         Link:=True | False, _
                         DisplayAsIcon:=True | False, _
                         IconFileName:="C:\Temp\icon.bmp", _
                         IconIndex:=1, _
                         IconLabel:="Text label", _
                         NoHTMLFormatting:=True | False

All the parameters are optional
Format - A string that specifies the Clipboard format of the data. msoClipboardFormat ?
Link - Whether to establish a link to the source of the pasted data. If the source data isn't suitable for linking or the source application doesn't support linking, this parameter is ignored. The default value is False.
DisplayAsIcon - Whether to display the pasted as an icon. The default value is False.
IconFileName - The name of the file that contains the icon to use if DisplayAsIcon is True.
IconIndex - The index number of the icon within the icon file.
IconLabel - The text label of the icon.
NoHTMLFormatting - Whether to remove all formatting, hyperlinks, and images from HTML. False to paste HTML as is. The default value is False. NoHTMLFormatting will only matter when Format = "HTML". In all other cases, NoHTMLFormatting will be ignored.


ActiveSheet.PasteSpecial Format:="Text" 
ActiveSheet.PasteSpecial Format:="HTML"
ActiveSheet.PasteSpecial Format:="Microsoft Word 8.0 Document Object"

This example pastes a Word document object and displays it as an icon.

Worksheets("Sheet1").Range("F5").Select 
ActiveSheet.PasteSpecial Format:="Microsoft Word 8.0 Document Object", _
                         DisplayAsIcon:=False,


Worksheet Object - Paste

Pastes the contents of the Clipboard onto the sheet.
If you don't specify the Destination argument, you must select the destination range before you use this method.
This method may modify the sheet selection, depending on the contents of the Clipboard.

Worksheets("Sheet1").Range("C1:C5").Copy 
ActiveSheet.Paste Destination:=Worksheets("Sheet1").Range("A2"), _
                  Link:=False

Destination - A Range object that specifies where the Clipboard contents should be pasted. If this argument is omitted, the current selection is used. This argument can be specified only if the contents of the Clipboard can be pasted into a range. If this argument is specified, the Link argument cannot be used.
Link - Whether to establish a link to the source of the pasted data. If this argument is specified, the Destination argument cannot be used. The default value is False.


Pasting into Word

Paste as a Windows Metafile
Only pastes in colour if there is a colour printer selected ??

Word.Selection.PasteSpecial Link:=False, _ 
                            DataType:=wdPasteDataType.wdPasteEnhancedMetafile, _
                            Placement:=0, _
                            DisplayAsIcon:=False

Word > Paragraphs > VBA Code > Paste Special
wdPasteMetafile
doesn't matter what the zoom % is.
image is automatically resized to fit the width of the page.


Selection.PasteAndFormat (Word.wdRecoveryType.wdSingleCellText) 

Pasting into PowerPoint

pptfaq.com/FAQ00826.htm


Excel > EMF > Word - wdPasteMetafile % is wrong
Excel > EMF > Word - wdPasteOLEObject % is right - creates a copy of workbook and embeds it into the word document.


cell range - create a picture of the range to get the size


CopyPicture option


© 2019 Better Solutions Limited. All Rights Reserved. © 2019 Better Solutions Limited TopPrevNext