Excel to Word VBA: Copying too many rows

areyoupat

New Member
Joined
Dec 10, 2010
Messages
5
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.
 

Some videos you may like

Excel Facts

Excel Wisdom
Using a mouse in Excel is the work equivalent of wearing a lanyard when you first get to college

areyoupat

New Member
Joined
Dec 10, 2010
Messages
5
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

New Member
Joined
Dec 10, 2010
Messages
5
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
 

Watch MrExcel Video

Forum statistics

Threads
1,108,720
Messages
5,524,462
Members
409,582
Latest member
riibuildersinc

This Week's Hot Topics

Top