Paste a dynamic range into MS Word

[frostii]

Hi again,

So I have the following code to paste a range from Excel to MS Word.

Sub Synopsis()
Dim tbl As Excel.Range
Dim WordApp As Word.Application
Dim myDoc As Word.Document
Dim WordTable As Word.Table

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

'Copy Range from Excel
  Set tbl = ThisWorkbook.Worksheets("Data").Range("B4:C10")
  tbl.Copy

'Create an Instance of MS Word
  On Error Resume Next
    
    'Is MS Word already opened?
      Set WordApp = GetObject(class:="Word.Application")
    
    'Clear the error between errors
      Err.Clear

    'If MS Word is not already open then open MS Word
      If WordApp Is Nothing Then Set WordApp = CreateObject(class:="Word.Application")
    
    'Handle if the Word Application is not found
      If Err.Number = 429 Then
        MsgBox "Microsoft Word could not be found, aborting."
        GoTo EndRoutine
      End If

  On Error GoTo 0
  
'Make MS Word Visible and Active
  WordApp.Visible = True
  WordApp.Activate
    
'Create a New Document
  Set myDoc = WordApp.Documents.Open("template.docx")
  
'Copy Excel Table Range
  tbl.Copy

'Paste Table into MS Word
  myDoc.Paragraphs(1).Range.PasteExcelTable _
    LinkedToExcel:=False, _
    WordFormatting:=False, _
    RTF:=False

'Autofit Table so it fits inside Word Document
  Set WordTable = myDoc.Tables(1)
  WordTable.AutoFitBehavior (wdAutoFitWindow)
   
EndRoutine:
'Optimize Code
  Application.ScreenUpdating = True
  Application.EnableEvents = True

'Clear The Clipboard
  Application.CutCopyMode = False

End Sub

Currently the range is B4:C10 for testing, but I need the range to be dynamic in nature. For example, it will always start at B4 but could finish at C10, F10, H10, etc.

Is there a way to alter the code to allow the range to start at B4 and end when there is no data left (at the bottom of the sheet)?

Thanks in advance.

[macropod]

That's just a matter of find where the last column & row is and incorporating that into the range definition. For example:

'Copy Range from Excel
  Dim r As Long, c As Long
  With ThisWorkbook.Worksheets("Data")
    With .UsedRange.Cells.SpecialCells(xlCellTypeLastCell)
      r = .Row
      c = .Column
    End With
    .Range(.Range("A1", .Cells(r, c)).Address).Copy
  End With

[frostii]

Thanks for the reply macropod!
Please excuse my noobish-ness, but where in the code should I stick your version?
Or, do I just replace the 'Copy Range from Excel' bit?
Thanks again.

[macropod]

That code would replace either your first or second 'Copy Range from Excel' equivalent I'd probably go for the latter). You should delete the other 'Copy Range from Excel' instance, along with 'Dim tbl As Excel.Range', as that's no longer needed.

[frostii]

I replaced the second 'Copy Range' part, removed the first 'Copy Range' part and everything is working beautifully.
Thanks so much, will mark as SOLVED.