Type mismatch when trying to paste range

[Homegrownandy]

Note: This isnt code within access, Im using data from access in an excel table which I would like to then paste into a word document.



This is the code I currently am using:

Sub ExcelRangeToWord()


Dim objWord As Object
Dim ws As Worksheet

    Set ws = ThisWorkbook.Sheets("RISKS")
    Set objWord = CreateObject("Word.Application")

    objWord.Visible = True


'Optimize Codee
  Application.ScreenUpdating = False
  Application.EnableEvents = False


  'open the word doc
      objWord.Documents.Open "C:\Users\ah\Desktop\RAM-HS-007  BAU RAMS.docx" 'change as required


'pastes the value of cell I19 at the "heatlosses" bookmark
    With objWord.ActiveDocument
        .Bookmarks("RISKS").Range.Text = ws.Range("A1:I18").Value
    End With

'Optimize Code
Set objWord = Nothing
  Application.ScreenUpdating = True
  Application.EnableEvents = True

'Clear The Clipboard
  Application.CutCopyMode = False

End Sub

This code will work fine if "ws.Range("A1:I18").Value" is changed to "ws.Range("A1").Value" but abviously it only displayes the contents of cell A1 in the word document.

Thats the first problem, pasting in the table. The second problem would be definind the range. The range may not need to be up to row 18. The document is for showing risks on a site (pulled from the DB into excel). If there is only 1 risk then we dont need to copy in 18 rows. (I hope this makes sense)

Any help appreciated, even if its not the answer.

Thanks a lot, Andy.

[ranman256]

Copy the range to the clipboard,
then paste clipboard to word.

[Homegrownandy]

Thanks a lot. I have that working now.