Copy from excel into word table.

[Homegrownandy]

The code shown below was working until today, Now I get the error:

Method 'Range' of object '_Worksheet' failed

Or, if I step into the code line by line I get this:

Application-defined or object-defined error

I am on the sheet risks at the time (I have read this can cause issue) I'm unsure what's causing it.

Note: previously this was test data, now its external data from access. I cant find a reliable way to insert rows into a table from access into word without using excel as the middle man. If I do it straight from access I get formatting issues and unwanted column headers.

Sub ExcelRangeToWord()

Dim objWord As Object
Dim ws As Worksheet
Dim lngLastRow As Long
On Error GoTo Errorcatch
lngLastRow = Sheets("RISKS").Range("A65535").End(xlUp).Row
Debug.Print lngLastRow
    Set ws = ThisWorkbook.Sheets("RISKS")
    Set objWord = CreateObject("Word.Application")
    objWord.Visible = True
 
'Optimize Code
  Application.ScreenUpdating = False
  Application.EnableEvents = False
  ws.Range("A:2" & "H:" & lngLastRow).Copy
  
  'open the word doc
      objWord.Documents.Open "C:\Users\ah\Desktop\RAMS AUTOMATION\Import table test.docx" 'change as required

'pastes the value of cell at the bookmark
    With objWord.ActiveDocument
        .Bookmarks("RISKS").Range.Paste
    End With
'Optimize Code
Set objWord = Nothing
  Application.ScreenUpdating = True
  Application.EnableEvents = True
'Clear The Clipboard
  Application.CutCopyMode = False
Exit Sub
Errorcatch:
MsgBox Err.Description
End Sub

[Homegrownandy]

Okay, I have broke this code when trying to implement the lnglastrow variable. ( I forgot I even attempted it.)

If I manually define the range it does work..

This is where the error occurs ("A:2" & "H:" & lngLastRow)

If I debug.print lngLastRow It does show the correct value.

Any suggestions?

[Homegrownandy]

sorted: just confused myself with the colons after amending it a few days ago and not finishing what I was doing.