code to copy Excel to Word

**Dennisli2000** · 09-11-2012, 03:56 PM

Good afternoon, everyone
I have two ranges B2:C22 and B52:C72 in sheet named DataSource. I need to copy these two ranges to a Word file named MonthReport. Range B2:C22 will in the first page, range B52:C72 will be in the second page.
I want to write a code to copy and paste but could not figure out.
Appreciate your help.
DL

**Al Chara** · 09-11-2012, 10:08 PM

What is the full path for the MonthlyReport document?

**Al Chara** · 09-11-2012, 10:52 PM

I have to get going soon, so just change the path in the code to your desired location. Make sure to set a reference to Microsoft Word Object Library.

Please Login or Register  to view this content.

**Dennisli2000** · 09-12-2012, 09:39 AM

Thanks lot, have a good day.

**Dennisli2000** · 09-12-2012, 10:16 AM

Thanks again and I am back for more question:
Let me change the ranges:
Range 1: A1:G47
Range 2: I1:O47
Range 3: Q1:W47,
Range 4: Y1:AE47,
Etc.
For each range, there are 7 columns and 47 rows. There is one column distance between the ranges. Totally, there are 50 ranges.
Using your idea, I can write 50 cope/ paste code but it’s time consuming. Could you help me to write a loop to copy and paste the 50 ranges to Word? One range one page.
Thanks lot.
DL

**Al Chara** · 09-13-2012, 12:36 AM

Let me know if this works for you.

Please Login or Register  to view this content.

**Dennisli2000** · 09-13-2012, 09:15 AM

Appreciate your help, Al.

There are two errors:

Please Login or Register  to view this content.

gets the following error message: User Defined type not defined.
So I changed to Dim oDoc

Please Login or Register  to view this content.

gets the following error message: Run-time error ‘9118’ Parameter value was out of acceptable range.
I Comment Block it.
After the changes, it copies and pastes. But there are no page break for each range.
Any ideas?
DL

**Al Chara** · 09-13-2012, 09:50 AM

Make sure to set a reference to Microsoft Word Object Library. To do this, select Tools > References, in the Visual Basic Editor, and scroll down to find the Microsoft Word Object Library.

**Dennisli2000** · 09-14-2012, 09:35 AM

Thanks lot, Al
Do you mean the Reference in Word or Excel? I have checked Microsoft Word Object Library in Word VBA. But for Excel, the Reference is gray which means I can not check it.
Any ideas?
Have a good day.
DL

**Dennisli2000** · 09-24-2012, 03:05 PM

Good afternoon, AL
Thanks again but I have one more question:
From cell A51, there are another 50 ranges just like the ranges in row1 to row 47: each range has 7 columns and 46 rows. There is one column space between two adjacent ranges.
The sequence of copy and paste to Word should be:
A1:G47, A51:G97
I1:O47, I51:O97
Q1:W47, Q51:W97
Y1:AE47, Y51:AE97
Etc.
Appreciate you help.
DL