Copying / Pasting

Copying

Excel > Charts > VBA Code > Copying
Excel > Illustrations > VBA Code > Copying
Word > Paragraphs > VBA Code

Copying and Pasting a single value

The most intuitive way to copy data from one cell to another would be:

Range("A2").Select 
Selection.Copy 
Range("D2").Select 
ActiveSheet.Paste 

One of the most important aspects of VBA programming is remembering that you do not have to actually select the object you want to manipulate.
The code could therefore be changed to the following.

Range("A2").Copy 
Range("D2").Select 
ActiveSheet.Paste 

There are always several ways to accomplish the same thing in VBA.
The code could be reduced to two lines using the Paste Special method.

Range("A2").Copy 
Range("D2").PasteSpecial Paste:=xlValues 

It is actually possible to condense this action into a single line.
This is possible because the Copy method can take an optional argument that can represent the range to be copied to. The next two lines are equivalent.

Range("A2").Copy Destination:=Range("D2") 
Range("A2").Copy Range("D2") 

This copies the contents of cell "D2" and places them into the active cell.

Range("D2").Copy Destination:=ActiveCell    

Copying Data between Worksheets

It is possible to copy data between worksheets.
The can be done by including a reference to a particular worksheet. If no worksheet is specified then the active worksheet is used.

Worksheets("Sheet1").Range("A2").Copy Worksheets("Sheet2").Range("D2") 

Notice that you do not have to activate the necessary worksheets.
The following line of code copies the whole block of data to a worksheet Sheet2.

Range("A2").CurrentRegion.Copy Sheets("Sheet2").Range("D2") 

Copying Data between Workbooks

It is possible to copy data between worksheets.
The can be done by including a reference to a particular worksheet. If no worksheet is specified then the active worksheet is used.

Workbooks("Wbk1.xls".Worksheets("Sheet1").Range("A2").Copy Workbooks("Wbk2.xls").Worksheets("Sheet2").Range("D2") 

Long lines of code can sometimes be difficult to understand.
Another way to perform the same task is to use variables to store the necessary range objects.

Dim rgeCopyRange As Range 
Dim rgeToRange As Range 

Set rgeCopyRange = Workbooks("Wbk1.xls").Worksheets("Sheet1").Range("A2") 
Set rgeToRange = Workbooks("Wbk2.xls").Worksheets("Sheet2").Range("D2") 
rgeCopyRange.Copy rgeToRange 

Cancelling the Copy Mode

When you copy (or Cut) a range of cells a black dotted line appears around the area to help identify it.
This can be removed by switching the CutCopy property to False.

Application.CutCopyMode = xlCutCopyMode.False 

If you do not change this property back to false then the cell range that was last copied will remain flashing on the screen.

Range("A2").Copy 
Range("D2").Select 
ActiveSheet.Paste 
Application.CutCopyMode = xlCutCopyMode.False 

You do not need to reset this property if you use a single line statement to copy and paste.

Range("A2").Copy Range("D2") 

Copying Ranges as Pictures

to copy the selected worksheet range as a picture

Selection.CopyPicture Appearance:=xlPictureAppearance.xlScreen, Format:=xlCopyPictureFormat.xlPicture 

Freeze and unfreeze references when copying

Public arFormulas() As Variant 

Public Sub Freeze() 
    arFormulas = Selection.Formula 
End Sub 

Public Sub UnFreeze() 
    If (Not Not arFormulas) = 0 Then 
        Call MsgBox ("Error: No formulas were copied. Please use the Freeze routine first.") 
        Exit Sub 
    End If 

    Selection.Resize(UBound(arFormulas, 1), UBound(arFormulas, 2)).Value = arFormulas 
End Sub 

Paste Special - Range Object

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.

Paste Special - Worksheet Object

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, 

Paste - Worksheet Object

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
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

link - 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

Copy, Save Picture and Send

This copies a cell range, saves it as a picture and adds it to an email.

Public Sub SaveRangeAndSend() 
   Call Cells_ToPicture(Range("A1:B20"), "C:\Temp\myFile.png") 
   Call SendEmail("C:\Temp\myFile.png", "myFile.png") 
End Sub 

This code creates an Outlook email and embeds an image into the email body.
You need to add a reference to the Microsoft Office 16.0 Object Library.

Public Sub SendEmail(ByVal sFullPath As String, _ 
                     ByVal sFileName As String) 
Dim oOutlookApp As Outlook.Application 
Dim oMailItem As Outlook.MailItem 

    Set oOutlookApp = CreateObject("Outlook.Application") 
    Set oMailItem = oOutlookApp.CreateItem(OlItemType.olMailItem) 
    
    With oMailItem 
        .Subject = "mytitle" 
        .HTMLBody = "add some text<BR><BR><IMG src=""cid:" & sFileName & """>" 
        .Recipients.Add "myname@bettersolutions.com" 
        .Attachments.Add sFullPath, Type:=OlAttachmentType.olEmbeddeditem, Position:=0 
    End With 
    
    oMailItem.Display 
    'oMailItem.Send
   Exit Sub 
ErrorHandler: 
    MsgBox (Err.Number & " - " & Err.description) 
End Sub