I am trying to build an issue log from excel into word. Not quite sure what the problem is with the copy syntax.
When I run the code below I receive the following error: Run-time error '1004: Application-defined or object-defined error.
Sub CreateIssueLog()
Dim WordApp As Word.Application
Set WordApp = New Word.Application
Dim dNow
dNow = Format(Now, "yymmdd hhmmss")
With WordApp
.Visible = True
.Activate
.Documents.Add
With .Selection
.PageSetup.Orientation = wdOrientLandscape
.BoldRun
.Paragraphs.Alignment = 1
.Font.Size = 20
.TypeText "Plan: "
.TypeText dNow
.Font.Size = 16
.TypeParagraph
.TypeText "Issues"
Dim c As Range
'Counter
Dim i As Long
i = 1
For Each c In Worksheets("Issue").Range("J2:J109")
i = i + 1
If c.Value <> "" Then
If c.Value <> "Completed" Then
.TypeParagraph
Worksheets("Issue").Range("E1:J1").Copy
.Paste
Application.Wait (Now + TimeValue("0:00:01"))
Worksheets("Issue").Range(Cells(i, "E"), Cells(i, "J")).Copy
.Paste
End If
End If
Next c
End With
End With
WordApp.ActiveDocument.SaveAs2 Environ("userprofile") & "\Desktop\Issue Log " & dNow & ".docx"
'WordApp.ActiveDocument.Close
'WordApp.Quit
MsgBox "Issue Log Generated. Check desktop for latest version.", , "Complete!"
End Sub
The debugger jumps to this line with the appropriate number being passed to i. Also if I hard code i with a long I receive the same error.
Worksheets("Issue").Range(Cells(i, "E"), Cells(i, "J")).Copy