i’m hoping someone could help me change this code so that it creates word docs for every item in the data validation list. At the moment the code only creates one word doc at a time with manual list items selected. The list uses a source where I have pasted the list items in seperate cells in a single row. Im not sure how I can get VBA to loop through the list items and create the seperate word documents, where each item changes the cell data in the given table. Also, the list is located in a merged cell (B9:C9). Any help will be appreciated!

Here is the current code, where i have created a button and assigned the macro to:

Sub ExportTableToWord()
    Dim tbl As Range
    Dim wordApp As Object
    Dim wordDoc As Object
    Dim wordTable As Object
    Dim wordRange As Object ' Declare wordRange variable
    
    ' Define the range of your table in Excel
    Set tbl = ThisWorkbook.Sheets("Export Objectives to Word").Range("B7:C29")
    
    ' Create a new instance of Word application
    Set wordApp = CreateObject("Word.Application")
    wordApp.Visible = True ' Make Word visible
    
    ' Create a new Word document
    Set wordDoc = wordApp.Documents.Add
    
    ' Set the margin to "narrow"
    wordDoc.PageSetup.LeftMargin = wordApp.InchesToPoints(0.4) ' Set left margin to 0.5 inches
    wordDoc.PageSetup.RightMargin = wordApp.InchesToPoints(0.4) ' Set right margin to 0.5 inches
    wordDoc.PageSetup.TopMargin = wordApp.InchesToPoints(0.4) ' Set top margin to 0.5 inches
    wordDoc.PageSetup.BottomMargin = wordApp.InchesToPoints(0.4) ' Set bottom margin to 0.5 inches
    
    ' Insert a page break before pasting the table into Word
    Set wordRange = wordDoc.Range
    wordRange.Collapse Direction:=1 ' Collapse the range to the end
    wordRange.InsertBreak  ' Insert a page break
    
    ' Display a message indicating that the Word document was successfully created
    MsgBox "Word document was successfully created."
        
    ' Copy the table from Excel and paste it into Word
    tbl.Copy
    wordDoc.Range.PasteExcelTable LinkedToExcel:=False, WordFormatting:=False, RTF:=False
    
    ' Set the table properties in Word to match Excel
    Set wordTable = wordDoc.Tables(1) ' Assuming the pasted table is the first table in the document
    wordTable.AutoFitBehavior wdAutoFitWindow ' Auto-fit the table to the window
    
    ' Clean up
    Set tbl = Nothing
    Set wordDoc = Nothing
    Set wordApp = Nothing
    Set wordTable = Nothing
    Set wordRange = Nothing
    
End Sub

I tried looping through the list bu keep running into errors or not all list items being created!