Transfering Data To Word

[IcePirates]

Hello,

Im hoping someone can help me out ...My coding is below and it partially works; when you run the macro in Excel, the script opens a Word document but does not transfer what is in the Excel sheet to the word document...

The error I get an error that says, 'Sub-Script' out of range..It doesn't look to me like any of it is out of range...but maybe Im missing something...Does anyone see anything?

Option Explicit

Public Sub TransferData()
    ' This macro transfers the data range "A1:E11" to a table in Word
    ' Constants:
    ' docFullName = The full name of an already existing Word document
    ' Variables:
    ' doc = The Word document (assumed to be empty)
    ' i = A counter (for rows)
    ' j = A counter (for columns)
    ' tbl = A Word table
    ' wdRng = A Word range (the first paragraph of doc)
    ' wks = The worksheet "data" that contains the data range
    ' Const docFullName = "C:\OLE Automation\Word.doc" '
    Dim doc As Object
    Dim i As Long
    Dim j As Long
    Dim tbl As Object
    Dim wdApp As Object 'Only if you require a new document each time
    Dim wdRng As Object
    Dim wks As Worksheet
    ' Assing Word objects 'Only if you require a new document each time
    Set wdApp = CreateObject("Word.Application")
    wdApp.Visible = True
    Set doc = wdApp.Documents.Add
    ' Assign variables and objects
    ' Set doc = GetObject(docFullName) 'Only if you want a specific document
    Set wdRng = doc.Paragraphs(1).Range
    Set tbl = doc.Tables.Add(wdRng, 11, 5)
    Set wks = ThisWorkbook.Worksheets("data")
    ' Transfer the data
    With tbl
        For i = 1 To 11
            For j = 1 To 5
                .Cell(i, j) = wks.Cells(i, j)
            Next j
        Next i
    End With
    ' Save and close doc 'Only if you want a specific document
    ' Call doc.Save
    ' Call doc.Close(False)
    ' Clean
    Set doc = Nothing
    Set wks = Nothing
End Sub

And I have data in cells A1:E11 - So I was thinking that could be the issue but I double checked that and the data is there

[Kenneth Hobs]

First off, welcome to the forum!

Most likely you don't have a worksheet named "Data" in the workbook with the code.

[IcePirates]

haha - ahh what a silly mistake!

Thanks for the help!~