Selected Answer
Hi Benjamin(?) and welcome to the Forum
Here's a revised answer (for my expediency based your suggestion on the discussion below to have a template with repeated bookmarks for several labels).
You can do this by a loop using your header row A if your Word document "MarksheetTemplate.docx" has correspondingly named bookmarks (and is stored in the same folder as your workbook - as per your current code). Note that spaces aren't allowed in bookmarks so row A in the attached file now shows headings Box_number, WA_Country, WA_Street and WA_Zip and your Word file will need to have bookmarks matching all the top row values (A to O).
I've added a new variable iCol and instead of using Range("cell name") to refer to individual cells, I've used Cells(<row number>, <column number>) since it's easier to loop using that form e.g. Cells(1, 2) is cell B1.
The key changes from your code (/my previous code) are in bold below. The important bit is the loop section starting For iCol = 1 To 15 where the macro checks that the bookmark (like number_1) exists then adds the value to it from the row in question before deleting the bookmark and moving on. Note that your embedded tempklate has 3 groups of bookmarks so if you add a 4th row in your workbook, the macro will populate the first 3 lables then inform you it can't find the first of the missing 4th bookmarks. I leave you to extend your Word template.
I also used wdDoNotSaveChanges when closing Word (to avoid overwriting the template). It produces a single Word document (without bookmarks) then alerts the user of the number of labels and file location. Here's the modified code used in the attached workbook, complete with extra comments (generally to right of code) for your guidance:
Sub SendToWord()
'Declare variables for this automation
Dim wd As Word.Application 'Word Application
Dim wDOC As Word.Document ' Word Document
Dim iRow As Long ' Variable to hold the starting row and loop through all records in the table
Dim iCol As Long ' Variable to loop through record columns in the table
Dim sh As Worksheet ' Worksheet variable to refer to the sheet where we have stored the table
Dim TargBook As String ' Variable to hold target bookmark name in Word
Dim DocName As String 'Variable for output Word file and path
Set wd = New Word.Application 'Start word and a new document
Set sh = ThisWorkbook.Sheets("Sheet1") 'set worksheet where table is available
iRow = 2 ' Initialize iRow with 2 as our data is starting from row number 2 from the table
Do While sh.Cells(iRow, 1).Value <> "" ' Do the following while there's something in column "A"
Set wdDOC = wd.Documents.Open(ThisWorkbook.Path & "\MarksheetTemplate.docx") 'Open the word template
wd.Visible = False 'without showing on screen
'Code to insert values from table to a specific bookmark in word application
On Error Resume Next
For iCol = 1 To 15 'from column A to O...
TargBook = sh.Cells(1, iCol).Value & "_" & iRow - 1 'create target bookmark name
If wdDOC.Bookmarks.Exists(TargBook) = True Then 'check header bookmark exists then...
wd.Selection.Goto what:=wdGoToBookmark, Name:=TargBook
wd.Selection.TypeText Text:=sh.Cells(iRow, iCol).Value 'take column value from loop row
wdDOC.Bookmarks(TargBook).Delete ' delete bookmark
Else
n = MsgBox("Please check template; cannot find Word bookmark: " & TargBook, vbOKOnly) ' Tell user something's missing
Exit Do 'then stop populating Word document
End If
Next iCol
iRow = iRow + 1
Loop
If sh.Cells(1, 17).Value = "" Then sh.Cells(1, 17).Value = "MyLabels" 'give filename if Q1 is empty
DocName = ThisWorkbook.Path & "\" & sh.Cells(1, 17).Value & ".docx" 'combine path and document name
wdDOC.SaveAs (DocName) 'Close the word file
wdDOC.Close (wdDoNotSaveChanges) ' don't overwrite template
Set wdDOC = Nothing 'Release the memory of wddoc
wd.Quit 'Close MS Word Application
Set wd = Nothing 'Release the memory allocated to WD variable
n = MsgBox((iRow - 2) & " Labels have been created successfully in file " & DocName, vbOKOnly) ' Tell the user how many labels were made
End Sub
Other users should note that the code relies on the Microsoft Word 16.0 Object library (or other version) being installed already (via VB Editor/Tool/References).
You could/should make further improvements like checking if the output document already exists (if you don't want to overwrite it) but I hope this works for you.
