VBA code for copying excel ranges into an existing Word template

[rjak]

Hi everyone

I am trying to copy from an existing excel template a specific range into an existing word doc. I get the error type 13 "Type mismatch". The error type 13 only pops up if I define more than one cell to copy.

Sub CreatWordReport()
Dim objWord
Dim objDoc
Dim objRange
Dim ws As Worksheet
Set ws = ThisWorkbook.Sheets("Summary")
Set objWord = CreateObject("Word.Application")
Set objDoc = objWord.Documents.Open("C:\Users\xxx\Desktop\test1.docx")
objWord.Visible = True
Set objRange = objDoc.Bookmarks("ManagementSummary").Range
objRange.InsertAfter ws.Range("A2", Range("A2").End(xlDown).End(xlToRight)).Value
End Sub

Does anyone see the mistake or the reason why I get this kind of error?

Thanks a lot for answering

 

[Yongle]

Try this instead

Sub CreatWordReport()
    Dim objWord
    Dim objDoc
    Dim objRange
    Dim ws As Worksheet
    Set ws = ThisWorkbook.Sheets("Summary")
    Set objWord = CreateObject("Word.Application")
    Set objDoc = objWord.Documents.Open("C:\TestArea\test1.docx")
    objWord.Visible = True
    Set objRange = objDoc.Bookmarks("ManagementSummary").Range
  
    ws.Range("A2", Range("A2").End(xlDown).End(xlToRight)).Copy
    objRange.PasteExcelTable False, False, False
End Sub

Above may not paste how you want - but there are several other (Word) paste methods

Range.Paste method (Word)

 

[rjak]

Try this instead

Sub CreatWordReport()
    Dim objWord
    Dim objDoc
    Dim objRange
    Dim ws As Worksheet
    Set ws = ThisWorkbook.Sheets("Summary")
    Set objWord = CreateObject("Word.Application")
    Set objDoc = objWord.Documents.Open("C:\TestArea\test1.docx")
    objWord.Visible = True
    Set objRange = objDoc.Bookmarks("ManagementSummary").Range
 
    ws.Range("A2", Range("A2").End(xlDown).End(xlToRight)).Copy
    objRange.PasteExcelTable False, False, False
End Sub

Above may not paste how you want - but there are several other (Word) paste methods

Range.Paste method (Word)

View attachment 7736

Hi Jongle

Thanks a lot for your clarification.