Copy-Paste Range Only with Values from Excel into Word
<p>I have been using this site for some time and usually research all of my questions but this one has had me stumped for a few days. </p>

<p>I have an Excel workbook with sheets that will allow the user to input specific data, and once the macro is used will create a Word doc formatted specifically for each macro.</p>

<p>I am trying to copy a range from excel but not all rows will be completed. The Range is currently 25 rows but most users will only use 8-12:</p>

<pre><code>Set tbl = ThisWorkbook.Worksheets(Sheet4.Name).Range("A8:D33")
</code></pre>

<p>I can copy this range and paste it into Word as a table but I am getting the empty rows as well. In Excel I would simply .PasteSpecial Paste:=xlPasteValues but this is not compatible with Word formatting.</p>

<p>Below is my current code to copy the range into my Word document:</p>

<pre><code>With Doc.Paragraphs(21).Range
    tbl.Copy
    Doc.Paragraphs(21).Range.PasteExcelTable LinkedToExcel:=False, _
    WordFormatting:=False, _
    RTF:=False
    '.PageSetup.LeftMargin = "40"
    '.PageSetup.RightMargin = "40"

    Set wtbl = Doc.Tables(1)
    wtbl.AutoFitBehavior (wdAutoFitContent)
    wtbl.Borders.Enable = True
        With wtbl.Rows(1)
            .Height = 10
            .Cells.VerticalAlignment = wdAlignVerticalBottom
        End With
    'wtbl.Rows.Cells.VerticalAlignment = wdCellAlignVerticalBottom
    'wtbl.Rows.Height = 15

    'Doc.Paragraphs(23).AutoFitBehavior (wdAutoFitWindow)

End With
</code></pre>