Copy from Excel and paste to Word - Troubleshooting

[Engineer123456]

Hello all,

So I have a working vba code that takes a bunch of cell values in excel and places them in a specific word document I created. These values are being placed where I put bookmarks in the word document.

My problem is, about 30-40% of the time I try to run the code, it freezes up and gives and error. When I debug it, it will show a random line of code that I know works to be the source of the issue. I know my code is working because it will run all the way through more than half the time with no problem. See below for my entire code and for the error messages:

Sub ConvertToWord()

Dim WrdDoc As Word.Document

'Reference Word Template
Set WrdDoc = GetObject("R:\Telecom\Structural\Analysis Templates\Mount Analysis\VBA\Automated Report Generation\Mount Analysis Report Template.docm")

Application.Wait (Now + TimeValue("0:00:01"))

'Select, copy and paste Cover Page
ActiveDocument.Bookmarks("ReportType").Select
ThisWorkbook.Worksheets("Word Report Preview").Range("K14").Copy
WrdDoc.ActiveWindow.Selection.PasteAndFormat Type:=wdKeepSourceFormatting
If ThisWorkbook.Worksheets("Word Report Preview").Range("K15").Value = "" Then
Else
ThisWorkbook.Worksheets("Word Report Preview").Range("K15").Copy
WrdDoc.ActiveWindow.Selection.PasteAndFormat Type:=wdKeepSourceFormatting
End If

ActiveDocument.Bookmarks("SiteInfo").Select
ThisWorkbook.Worksheets("Word Report Preview").Range("K18").Copy
WrdDoc.ActiveWindow.Selection.PasteAndFormat Type:=wdKeepSourceFormatting
If ThisWorkbook.Worksheets("Word Report Preview").Range("K19").Value = "" Then
Else
ThisWorkbook.Worksheets("Word Report Preview").Range("K19").Copy
WrdDoc.ActiveWindow.Selection.PasteAndFormat Type:=wdKeepSourceFormatting
End If
ThisWorkbook.Worksheets("Word Report Preview").Range("K20").Copy
WrdDoc.ActiveWindow.Selection.PasteAndFormat Type:=wdKeepSourceFormatting
ThisWorkbook.Worksheets("Word Report Preview").Range("K21").Copy
WrdDoc.ActiveWindow.Selection.PasteAndFormat Type:=wdKeepSourceFormatting
ThisWorkbook.Worksheets("Word Report Preview").Range("K22").Copy
WrdDoc.ActiveWindow.Selection.PasteAndFormat Type:=wdKeepSourceFormatting
ThisWorkbook.Worksheets("Word Report Preview").Range("K23").Copy
WrdDoc.ActiveWindow.Selection.PasteAndFormat Type:=wdKeepSourceFormatting

ActiveDocument.Bookmarks("Utilization").Select
If ThisWorkbook.Worksheets("Word Report Preview").Range("K25").Value = "" Then
Else
ThisWorkbook.Worksheets("Word Report Preview").Range("K25").Copy
WrdDoc.ActiveWindow.Selection.PasteAndFormat Type:=wdKeepSourceFormatting
End If
If ThisWorkbook.Worksheets("Word Report Preview").Range("K26").Value = "" Then
Else
ThisWorkbook.Worksheets("Word Report Preview").Range("K26").Copy
WrdDoc.ActiveWindow.Selection.PasteAndFormat Type:=wdKeepSourceFormatting
End If
If ThisWorkbook.Worksheets("Word Report Preview").Range("K27").Value = "" Then
Else
ThisWorkbook.Worksheets("Word Report Preview").Range("K27").Copy
WrdDoc.ActiveWindow.Selection.PasteAndFormat Type:=wdKeepSourceFormatting
End If

ActiveDocument.Bookmarks("Client").Select
ThisWorkbook.Worksheets("Word Report Preview").Range("K33").Copy
WrdDoc.ActiveWindow.Selection.PasteAndFormat Type:=wdKeepSourceFormatting
ThisWorkbook.Worksheets("Word Report Preview").Range("K34").Copy
WrdDoc.ActiveWindow.Selection.PasteAndFormat Type:=wdKeepSourceFormatting
ThisWorkbook.Worksheets("Word Report Preview").Range("K35").Copy
WrdDoc.ActiveWindow.Selection.PasteAndFormat Type:=wdKeepSourceFormatting

ActiveDocument.Bookmarks("MaserOffice").Select
ThisWorkbook.Worksheets("Word Report Preview").Range("K39").Copy
WrdDoc.ActiveWindow.Selection.PasteAndFormat Type:=wdKeepSourceFormatting
ThisWorkbook.Worksheets("Word Report Preview").Range("K40").Copy
WrdDoc.ActiveWindow.Selection.PasteAndFormat Type:=wdKeepSourceFormatting
ThisWorkbook.Worksheets("Word Report Preview").Range("K41").Copy
WrdDoc.ActiveWindow.Selection.PasteAndFormat Type:=wdKeepSourceFormatting

ActiveDocument.Bookmarks("Footer").Select
ThisWorkbook.Worksheets("Word Report Preview").Range("K46").Copy
WrdDoc.ActiveWindow.Selection.PasteAndFormat Type:=wdKeepSourceFormatting
ThisWorkbook.Worksheets("Word Report Preview").Range("K47").Copy
WrdDoc.ActiveWindow.Selection.PasteAndFormat Type:=wdKeepSourceFormatting

ActiveDocument.Bookmarks("FooterSecond").Select
ThisWorkbook.Worksheets("Word Report Preview").Range("K49").Copy
WrdDoc.ActiveWindow.Selection.PasteAndFormat Type:=wdKeepSourceFormatting

Application.Wait (Now + TimeValue("0:00:01"))

'Select, copy and paste page 2
ActiveDocument.Bookmarks("Objective").Select
ThisWorkbook.Worksheets("Word Report Preview").Range("V31").Copy
WrdDoc.ActiveWindow.Selection.PasteAndFormat Type:=wdFormatPlainText

ActiveDocument.Bookmarks("DocTable").Select
ThisWorkbook.Worksheets("Word Report").Range("C35:K43").Copy
WrdDoc.ActiveWindow.Selection.Paste

ActiveDocument.Bookmarks("IBC").Select
ThisWorkbook.Worksheets("Word Report Preview").Range("V9").Copy
WrdDoc.ActiveWindow.Selection.PasteAndFormat Type:=wdFormatPlainText

ActiveDocument.Bookmarks("TIA").Select
ThisWorkbook.Worksheets("Word Report Preview").Range("V10").Copy
WrdDoc.ActiveWindow.Selection.PasteAndFormat Type:=wdFormatPlainText

ActiveDocument.Bookmarks("WS").Select
ThisWorkbook.Worksheets("Word Report Preview").Range("V11").Copy
WrdDoc.ActiveWindow.Selection.PasteAndFormat Type:=wdFormatPlainText

ActiveDocument.Bookmarks("EC").Select
ThisWorkbook.Worksheets("Word Report Preview").Range("V12").Copy
WrdDoc.ActiveWindow.Selection.PasteAndFormat Type:=wdFormatPlainText

ActiveDocument.Bookmarks("RC").Select
ThisWorkbook.Worksheets("Word Report Preview").Range("V13").Copy
WrdDoc.ActiveWindow.Selection.PasteAndFormat Type:=wdFormatPlainText

ActiveDocument.Bookmarks("TF").Select
ThisWorkbook.Worksheets("Word Report Preview").Range("V14").Copy
WrdDoc.ActiveWindow.Selection.PasteAndFormat Type:=wdFormatPlainText

ActiveDocument.Bookmarks("MBE").Select
ThisWorkbook.Worksheets("Word Report Preview").Range("V15").Copy
WrdDoc.ActiveWindow.Selection.PasteAndFormat Type:=wdFormatPlainText

ActiveDocument.Bookmarks("IWS").Select
ThisWorkbook.Worksheets("Word Report Preview").Range("V16").Copy
WrdDoc.ActiveWindow.Selection.PasteAndFormat Type:=wdFormatPlainText

ActiveDocument.Bookmarks("DIT").Select
ThisWorkbook.Worksheets("Word Report Preview").Range("V17").Copy
WrdDoc.ActiveWindow.Selection.PasteAndFormat Type:=wdFormatPlainText

ActiveDocument.Bookmarks("MWS").Select
ThisWorkbook.Worksheets("Word Report Preview").Range("V18").Copy
WrdDoc.ActiveWindow.Selection.PasteAndFormat Type:=wdFormatPlainText

ActiveDocument.Bookmarks("ML").Select
ThisWorkbook.Worksheets("Word Report Preview").Range("V19").Copy
WrdDoc.ActiveWindow.Selection.PasteAndFormat Type:=wdFormatPlainText

ActiveDocument.Bookmarks("MLL").Select
ThisWorkbook.Worksheets("Word Report Preview").Range("V20").Copy
WrdDoc.ActiveWindow.Selection.PasteAndFormat Type:=wdFormatPlainText

ActiveDocument.Bookmarks("SN").Select
ThisWorkbook.Worksheets("Word Report Preview").Range("V21").Copy
WrdDoc.ActiveWindow.Selection.PasteAndFormat Type:=wdFormatPlainText

ActiveDocument.Bookmarks("SC").Select
ThisWorkbook.Worksheets("Word Report Preview").Range("V22").Copy
WrdDoc.ActiveWindow.Selection.PasteAndFormat Type:=wdFormatPlainText

ActiveDocument.Bookmarks("SS").Select
ThisWorkbook.Worksheets("Word Report Preview").Range("V23").Copy
WrdDoc.ActiveWindow.Selection.PasteAndFormat Type:=wdFormatPlainText

ActiveDocument.Bookmarks("S").Select
ThisWorkbook.Worksheets("Word Report Preview").Range("V24").Copy
WrdDoc.ActiveWindow.Selection.PasteAndFormat Type:=wdFormatPlainText

Application.Wait (Now + TimeValue("0:00:01"))

'Select, copy and paste page 3
ActiveDocument.Bookmarks("Loading").Select
ThisWorkbook.Worksheets("Word Report Preview").Range("C114").Copy
WrdDoc.ActiveWindow.Selection.PasteAndFormat Type:=wdKeepSourceFormatting
ThisWorkbook.Worksheets("Word Report").Range("N65:T86").Copy
WrdDoc.ActiveWindow.Selection.Paste


If ThisWorkbook.Worksheets("Word Report Preview").Range("D138").Value = "" Then
Else
ThisWorkbook.Worksheets("Word Report Preview").Range("D138").Copy
WrdDoc.ActiveWindow.Selection.PasteAndFormat Type:=wdKeepSourceFormatting
End If

ActiveDocument.Bookmarks("AnalysisApproach").Select
ThisWorkbook.Worksheets("Word Report Preview").Range("V7").Copy
WrdDoc.ActiveWindow.Selection.PasteAndFormat Type:=wdFormatPlainText

Application.Wait (Now + TimeValue("0:00:01"))

'Select, copy and paste page 4
ActiveDocument.Bookmarks("Two").Select
ThisWorkbook.Worksheets("Word Report Preview").Range("V26").Copy
WrdDoc.ActiveWindow.Selection.PasteAndFormat Type:=wdFormatPlainText

ActiveDocument.Bookmarks("Three").Select
ThisWorkbook.Worksheets("Word Report Preview").Range("V27").Copy
WrdDoc.ActiveWindow.Selection.PasteAndFormat Type:=wdFormatPlainText

ActiveDocument.Bookmarks("Seven").Select
ThisWorkbook.Worksheets("Word Report Preview").Range("V28").Copy
WrdDoc.ActiveWindow.Selection.PasteAndFormat Type:=wdFormatPlainText

ActiveDocument.Bookmarks("Eight").Select
ThisWorkbook.Worksheets("Word Report Preview").Range("V29").Copy
WrdDoc.ActiveWindow.Selection.PasteAndFormat Type:=wdFormatPlainText

ActiveDocument.Bookmarks("Nine").Select
ThisWorkbook.Worksheets("Word Report Preview").Range("V30").Copy
WrdDoc.ActiveWindow.Selection.PasteAndFormat Type:=wdFormatPlainText

Application.Wait (Now + TimeValue("0:00:01"))

'Select, copy and paste page 5
ActiveDocument.Bookmarks("Results").Select
ThisWorkbook.Worksheets("Word Report").Range("C129:K153").Copy
WrdDoc.ActiveWindow.Selection.Paste
If ThisWorkbook.Worksheets("Word Report Preview").Range("V32").Value = "" Then
Else
ThisWorkbook.Worksheets("Word Report Preview").Range("V32").Copy
WrdDoc.ActiveWindow.Selection.PasteAndFormat Type:=wdKeepSourceFormatting
End If

ActiveDocument.Bookmarks("Recommendation").Select
ThisWorkbook.Worksheets("Word Report Preview").Range("V33").Copy
WrdDoc.ActiveWindow.Selection.PasteAndFormat Type:=wdKeepSourceFormatting
ThisWorkbook.Worksheets("Word Report Preview").Range("V34").Copy
WrdDoc.ActiveWindow.Selection.PasteAndFormat Type:=wdKeepSourceFormatting

ActiveDocument.Bookmarks("RecommendationOne").Select
If ThisWorkbook.Worksheets("Word Report Preview").Range("V35").Value = "" Then
Else
ThisWorkbook.Worksheets("Word Report Preview").Range("V35").Copy
WrdDoc.ActiveWindow.Selection.PasteAndFormat Type:=wdKeepSourceFormatting
End If
If ThisWorkbook.Worksheets("Word Report Preview").Range("V36").Value = "" Then
Else
ThisWorkbook.Worksheets("Word Report Preview").Range("V36").Copy
WrdDoc.ActiveWindow.Selection.PasteAndFormat Type:=wdKeepSourceFormatting
End If

ActiveDocument.Bookmarks("RecommendationTwo").Select
ThisWorkbook.Worksheets("Word Report Preview").Range("V38").Copy
WrdDoc.ActiveWindow.Selection.PasteAndFormat Type:=wdKeepSourceFormatting
ThisWorkbook.Worksheets("Word Report Preview").Range("V39").Copy
WrdDoc.ActiveWindow.Selection.PasteAndFormat Type:=wdKeepSourceFormatting

Application.Wait (Now + TimeValue("0:00:01"))

'Select, copy and paste page 6
ActiveDocument.Bookmarks("MountOne").Select
ThisWorkbook.Worksheets("Word Report Preview").Range("V45").Copy
WrdDoc.ActiveWindow.Selection.PasteAndFormat Type:=wdFormatPlainText

ActiveDocument.Bookmarks("MountTwo").Select
ThisWorkbook.Worksheets("Word Report Preview").Range("V47").Copy
WrdDoc.ActiveWindow.Selection.PasteAndFormat Type:=wdFormatPlainText

ActiveDocument.Bookmarks("MountThree").Select
ThisWorkbook.Worksheets("Word Report Preview").Range("V47").Copy
WrdDoc.ActiveWindow.Selection.PasteAndFormat Type:=wdFormatPlainText

ActiveDocument.Bookmarks("MountFour").Select
ThisWorkbook.Worksheets("Word Report Preview").Range("V47").Copy
WrdDoc.ActiveWindow.Selection.PasteAndFormat Type:=wdFormatPlainText

ActiveDocument.Bookmarks("ModNote").Select
If ThisWorkbook.Worksheets("Word Report Preview").Range("V44").Value = "" Then
Else
ThisWorkbook.Worksheets("Word Report Preview").Range("V44").Copy
WrdDoc.ActiveWindow.Selection.PasteAndFormat Type:=wdFormatPlainText
End If
ThisWorkbook.Worksheets("Word Report Preview").Range("V44").Copy
WrdDoc.ActiveWindow.Selection.PasteAndFormat Type:=wdFormatPlainText

Application.Wait (Now + TimeValue("0:00:01"))

'Select, copy and paste Header
ActiveDocument.Bookmarks("Header").Select
ThisWorkbook.Worksheets("Word Report Preview").Range("K56").Copy
WrdDoc.ActiveWindow.Selection.PasteAndFormat Type:=wdKeepSourceFormatting
ThisWorkbook.Worksheets("Word Report Preview").Range("K57").Copy
WrdDoc.ActiveWindow.Selection.PasteAndFormat Type:=wdKeepSourceFormatting
ThisWorkbook.Worksheets("Word Report Preview").Range("K58").Copy
WrdDoc.ActiveWindow.Selection.PasteAndFormat Type:=wdKeepSourceFormatting

End Sub

 

[CSmith]

Have you tried this with:
.Application.ScreenUpdating = False ' At start of sub
.Application.ScreenUpdating = True ' At Error / end of sub

Also it sounds like a particular .Range"<>".Copy might be Null / Empty.

 

[Michael M]

You might need to either increase the Wait time OR insert it a little more frequently

 

[GWteB]

You're using the Windows clipboard instead of direct interaction between Word and Excel on VBA level. That's what causes your problems. Try to avoid the .Selection in Excel and don't use the clipboard. Further more, just ONE application (either Excel or Word) must be the "master" (the other the "slave") since VBA (ie the __.dll files below the surface of what we are doing on VBA level) is not reentrant.

 

[NdNoviceHlp]

U seem to be copy/pasting multiple values to the same bookmark? Instead of selecting bookmarks....

With ActiveDocument.bookmarks("bookmarkname").Range
.Paste
End With

Instead of …...

If ThisWorkbook.Worksheets("Word Report Preview").Range("K15").Value = "" Then
 Else
If ThisWorkbook.Worksheets("Word Report Preview").Range("K15").Value <> vbnullstring Then

Your doing too much copy and pasting and your clipboard is crashing. You can either code to not copy and paste or add code to fix your clipboard. HTH. Dave

 

[NdNoviceHlp]

Here's an example of how to get rid of the copy and pasting...

Dim ARange As Range
If ActiveDocument.Bookmarks.Exists("SiteInfo") Then
Set ARange = ActiveDocument.Bookmarks("SiteInfo").Range
ActiveDocument.Bookmarks("SiteInfo").Range.Text = _
CStr(ThisWorkbook.Worksheets("Word Report Preview").Range("K18"))
ActiveDocument.Bookmarks.Add Name:="SiteInfo", Range:=ARange
End If

I'll post a link to some clipboard code. Dave
link: VBA code to merge workbooks quits unexpectedly