Copying Multiple Excel ranges to Word doc

stroffso

Board Regular
Joined
Jul 12, 2016
Messages
160
Office Version
  1. 365
Platform
  1. Windows
Hi All,
I have a number of ranges which I would like to paste into word doc, when i do one it works fine however on the 2nd range it doesnt seem to work. So ideally it would paste the first range, then do a few spaces then the second range and so on. Would be good to have the page in landscape too. Below is the code I used.


Sub Excel_to_Word()
Dim appWord As Word.Application


Set appWord = New Word.Application


appWord.Visible = True


Range("C11:H13").Copy
appWord.Documents.Add.Content.Paste


Range("C20:L141").Copy


appWord.Documents.Add.Content.Paste


End Sub
 

Excel Facts

Wildcard in VLOOKUP
Use =VLOOKUP("Apple*" to find apple, Apple, or applesauce
Try something based on:
Code:
Sub Excel_to_Word()
Dim wdApp As New Word.Application, wdDoc As Word.Document
With wdApp
  .Visible = True
  Set wdDoc = .Documents.Add
  With wdDoc.Range
    ActiveSheet.Range("C11:H13").Copy
    .Characters.Last.Paste
    .InsertAfter vbCr & vbCr
    ActiveSheet.Range("C20:L141").Copy
    .Characters.Last.Paste
  End With
  .Activate
End With
Set wdDoc = Nothing: Set wdApp = Nothing
End Sub
 
Last edited:
Upvote 0
Thanks but it returns an error, says it doesnt support this property or method, relating to the following row in your code.

I tried this instead and it seems to do the trick, thanks for your help

wdDoc.Content.InsertParagraphAfter
 
Upvote 0
That is inefficient compared to the revision I posted.

I had posted that without seeing your update. Both seem to do exactly the same thing so all good, Do you know is there then a way of shrinking everything you have pasted to fit the doc? Currently they are all going way outside the margins?

thanks
 
Last edited:
Upvote 0
You could change the paste format, so you paste an Excel worksheet object rather than a Word table. Word should auto-scale that to fit within the left/right margins. The potential downside is that an embedded Excel worksheet object cannot span a page boundary.
 
Upvote 0

Forum statistics

Threads
1,213,530
Messages
6,114,162
Members
448,554
Latest member
Gleisner2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top