Loop problem

[fabcat1]

I'm creating a macro to generate Word documents and fill them with the content of an Excel table using bookmarks.

So far the macro successfully fill the first file, fill the bookmarks with the content of the second row.
It then create the second file but don't fill the bookmarks with the content of the third row.

I have the following error message " the requested member of the collection does not exists"
when I check i = 3

I have the feelong the loop is not set up correctly but I can't find out how to fix this

Thanks for your help

Here is my code :


Sub FillBookmarksWithLoop()


' définir les objets
Dim objWord As Object
Dim ws As Worksheet
Dim i As Integer
Dim lastrow As Integer

'for i = 2 to lastrow


' ouvrir la feuille Excel où sont les données
Set ws = ThisWorkbook.Sheets("Data")

' ouvrir Word
Set objWord = CreateObject("Word.Application")

objWord.Visible = True

' ouvrir le template
objWord.Documents.Open "C:\Users\CTN7400\Desktop\Moulinettes\Scope Statement\Project scope Template.docx" ' change as required


With objWord.ActiveDocument

For i = 2 To 3

' copier dans le bookmark correspondant le contenu de la cellule
.Bookmarks("Site_Code").Range.Text = ws.Cells(i, 2)
.Bookmarks("Site_Name").Range.Text = ws.Cells(i, 3)
.Bookmarks("Demand_ref").Range.Text = ws.Cells(i, 4)
.Bookmarks("Project_Title").Range.Text = ws.Cells(i, 5)
.Bookmarks("Localisation").Range.Text = ws.Cells(i, 6)
.Bookmarks("Requestor").Range.Text = ws.Cells(i, 7)
.Bookmarks("Program_Manager").Range.Text = ws.Cells(i, 8)
.Bookmarks("SAP_Code").Range.Text = ws.Cells(i, 9)
.Bookmarks("Request_date").Range.Text = ws.Cells(i, 10)
.Bookmarks("Target_date_Study").Range.Text = ws.Cells(i, 11)
.Bookmarks("Target_date_Build").Range.Text = ws.Cells(i, 12)
.Bookmarks("Project_Description").Range.Text = ws.Cells(i, 13)
.Bookmarks("Objectives_and_deliverables").Range.Text = ws.Cells(i, 14)
.Bookmarks("Out_of_scope").Range.Text = ws.Cells(i, 15)
.Bookmarks("Assumptions").Range.Text = ws.Cells(i, 16)
.Bookmarks("Budget_Material").Range.Text = ws.Cells(i, 17)
.Bookmarks("Budget_Partner").Range.Text = ws.Cells(i, 18)
.Bookmarks("Budget_Internal_Ressources").Range.Text = ws.Cells(i, 19)
.Bookmarks("Budget_Total").Range.Text = ws.Cells(i, 20)

' sauver le fichier Word
.SaveAs ("C:\Users\CTN7400\Desktop\Moulinettes\Scope Statement\" & ws.Cells(i, 2) & " - " & ws.Cells(i, 3) & " -6 Scope_Statement.docx")


' ouvrir le template
objWord.Documents.Open "C:\Users\CTN7400\Desktop\Moulinettes\Scope Statement\Project scope Template.docx" ' change as required
Next

'With objWord.ActiveDocument



End With
End Sub

 

[Anthony47]

Thank you for the feedback
(but look at how the loop is organized and match it with your initial approach)

 

[fabcat1]

Thank you for the feedback
(but look at how the loop is organized and match it with your initial approach)

Thanks for the advice
I will have a close look to understand how it's structured

 

[fabcat1]

Problem fixed

This thread can be closed

 

[Anthony47]

It's up to you to mark the question solved: Mark as Solution

 

[smozgur]

Problem fixed

This thread can be closed

I switched the marked solution with the post that answered this question.

@fabcat1 - when the problem is fixed, or in other words, your question is answered, please mark the post that answered your question instead of your final post to help future readers.