Copy-Paste Range Only with Values from Excel into Word

Question

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.

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.

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:

Set tbl = ThisWorkbook.Worksheets(Sheet4.Name).Range("A8:D33")

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.

Below is my current code to copy the range into my Word document:

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

Please disregard the few sections that have been commented out. They do not work, and are for formatting once I get the table to populate the date I want. I will play with those areas later. — B. Price
If "most users will only use 8-12" why don't you just dynamically size the Excel range instead? — Comintern
I apologize, I do not know what you mean by dynamically size the range? I need to allow up to 25 rows because there are one off scenarios that will require that many — B. Price
I mean, figure out how many rows you need to copy, and then just copy those rows. There's nothing that says .Range("A8:D33") needs to be hard-coded. — Comintern
It will vary each time. The user uses this sheet per letter generation, so one may require 8 rows, the next may require 15. This macro is used each time they generate a new word doc — B. Price

Comintern Comintern · Accepted Answer · 2016-10-25T18:11:14

You can find the last used cell in a column like this. Assuming that all columns will have data in them and that the data entry is from row 8 moving down, this should do the trick:

With Sheet4
    Dim lastRow As Long
    lastRow = .Range(.Cells(8, 1), .Cells(33, 1)).End(xlDown).Row
    Set tbl = .Range(.Cells(8, 1), .Cells(lastRow, 4))
End With

Copy-Paste Range Only with Values from Excel into Word

2 Answers