Copy from Excel and paste to Word - Troubleshooting

E

Engineer123456

New Member
Joined
Dec 20, 2019
Messages
3
Office Version
  1. 365
Platform
  1. Windows
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

Error Image.PNG
 

Excel Facts

Which lookup functions find a value equal or greater than the lookup value?
MATCH uses -1 to find larger value (lookup table must be sorted ZA). XLOOKUP uses 1 to find values greater and does not need to be sorted.
Sort by date Sort by votes
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.
 
Upvote 0
You might need to either increase the Wait time OR insert it a little more frequently
 
Upvote 0
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.
 
Upvote 0
U seem to be copy/pasting multiple values to the same bookmark? Instead of selecting bookmarks....
Code: 
With ActiveDocument.bookmarks("bookmarkname").Range
.Paste
End With
Instead of …...
Code: 
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
 
Upvote 0
Here's an example of how to get rid of the copy and pasting...
Code: 
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
 
Upvote 0
You must log in or register to reply here.

Similar threads

J
  • Question
Macro for copying data from multiple sheets to a common sheet based on a cell value
Replies
0
Views
205
jmk15315
J
K
  • Solved
Deleting content between two bookmarks in Word via Excel VBA
2
Replies
11
Views
1K
karoti99
K
E
  • Question
Help Cleaning Up / Optimizing VBA Code
Replies
6
Views
269
offthelip
O
K
  • Question
Enter after pasting cell value - VBA - Excel to Word
Replies
0
Views
294
katenguyen
K
B
  • Solved
Referencing Cells For PrToFileName (.PrintOut)
2
Replies
11
Views
559
boyko
B

Forum statistics

Threads
1,214,599
Messages
6,120,447
Members
448,966
Latest member
DannyC96

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top