Excel to Word VBA: Copying too many rows

[areyoupat]

I have 2 Columns (A & B) that I need to copy from excel and paste into word as RTF format. Although the number of rows differs daily, I will always need to begin the copy from cells A2 and B2. I am able to copy/paste everything into word just fine, but it selects too many rows when copying.

When I step into the macro, I see that at the point when it runs the code, LastRow = Cells(Rows.Count, "A").End(xlUp).Row it is counting the correct number of rows that I need to be copied.

But, when It runs the code to copy,
Range("A2:B2" & LastRow).Select
Selection.Copy
it is selecting too many rows, 225 to be exact.

Below is the entire code that I'm using to copy and paste into word, does anyone see anything in it that would be causing VBA to select too many rows? Any help would be much appreciated! I've been messing around with this code for about a day now and can't figure it out.

 

[areyoupat]

Here's the code that I'm using

Sub excel_to_word_TEST()

Application.DisplayAlerts = False

Dim i As Long, LastRow As Long

Dim Directory, Filename As String

Workbooks.Open Filename:="\\\\myname\clientfiles\client123\NewBusiness\documentname-" & Format(Date, "mmddyy") & ".xls"

LastRow = Cells(Rows.Count, "A").End(xlUp).Row

Range("A2:B2" & LastRow).Select
Selection.Copy

Set doc = CreateObject("Word.Application")
doc.Visible = True
doc.Documents.Add.Content.PasteExcelTable LinkedToExcel:=False, WordFormatting:=False, RTF:=True

End Sub

 

[areyoupat]

Never mind, I was able to figure it out. If anyone else runs into this issue, I used the below VBA code to count the rows and copy.


Range("a2:b2", Columns("a:b").Find("", , xlValues, xlWhole).Offset(-1)).Copy