Microsoft Excel

Ron de Bruin
Excel Automation

Microsoft MVP Program

Copy Excel data in Word file

On this page you find a workeround for a bug in Office 2011 when you paste Excel data into a Word file. Pasting as Enhanced Metafile is not working in Mac Office.

This is not working correct on a Mac when you use PasteSpecial but is working correct in Windows.

Sheets("Sheet1").Range("A1:B5").Copy
appWD.Selection.PasteSpecial Link:=False, DataType:=wdPasteEnhancedMetafile, Placement:= _
                                0, DisplayAsIcon:=False

On the Mac it will paste as a Table, so be aware that you have different results between Mac and Windows users. In both versions you are able to edit the data after the paste.

The basic code below paste as a picture and is working in Windows and on a Mac. it will copy two ranges in a new Word document and you get the same result in Windows and on a Mac. Remember that you can't change the data anymore because it is a picture.

Sub TestingMacAndWin()
    Dim appWD As Object
    Dim wddoc As Object

    On Error Resume Next
    Set appWD = GetObject(, "Word.application")    'gives error 429 if Word is not open
    If Err = 429 Then
        Set appWD = CreateObject("Word.application")    'creates a Word application
        Err.Clear
    End If

    Set wddoc = appWD.Documents.Add
    appWD.Visible = True

    With appWD.ActiveDocument.PageSetup
        .TopMargin = appWD.InchesToPoints(0.3)
        .BottomMargin = appWD.InchesToPoints(0.3)
        .LeftMargin = appWD.InchesToPoints(0.3)
        .RightMargin = appWD.InchesToPoints(0.3)
    End With

    Sheets("Sheet1").Range("A1:B5").CopyPicture xlScreen
    appWD.Selection.Paste

    Sheets("Sheet1").Range("A10:A14").CopyPicture xlScreen
    appWD.Selection.Paste

    appWD.Activate

End Sub