CODEWITHSUNDEEP
vbaexcelms-word
B. Price
B. Price

Reputation: 27

Copy-Paste Range Only with Values from Excel into Word

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

Upvotes: 1

Views: 1236

Answers (2)

JohnyL
JohnyL

Reputation: 7122

I think that CurrentRegion is your friend:

Set tbl = Range("A8").CurrentRegion

If you get any extra columns you don't need, you can always use Resize property.

Upvotes: 0

Comintern
Comintern

Reputation: 22185

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

Upvotes: 1

Related Questions