Copying Multiple Excel ranges to Word doc

[stroffso]

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

 

[Macropod]

Try something based on:

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

 

[stroffso]

Thanks but it returns an error, says it doesnt support this property or method, relating to the following row in your code.

.InsertAfter vbCr & vbCr

 

[Macropod]

I've edited the code. Try it now.

 

[stroffso]

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

 

[Macropod]

That is inefficient compared to the revision I posted.

 

[stroffso]

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

 

[Macropod]

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.