Okay, so what I am trying to do is take a template I have made in word and fill in the bookmarks using my excel workbook. The workbook contains several sheets, and the macro I have goes to each sheet and then opens a new template and fills it in based on what is in that sheet. HOWEVER, right now that isn't happening. It just seems to be taking random things and filling them into my bookmarks.
Code:
Sub SendToWord()
Dim objWord As Object
Dim objDoc As Object
Dim ws As Worksheet
Set objWord = CreateObject("Word.Application")
objWord.Visible = True
For Each ws In ActiveWorkbook.Worksheets
If ws.Name <> "equivalents" And ws.Name <> "Core Category" And ws.Name <> "Sheet4" Then ' dont open equivalents and Core Category
' create new document based on template
Set objDoc = objWord.Documents.Add("C:\Users\thomassa\Desktop\excel project\thistemplate.dotm") ' change as required
With objDoc
.Bookmarks("EKU_Major").Range.Text = ws.Name ' puts wksht name as the major
For k = 9 To 17
Select Case ws.Cells(1, k).Value
Case "Written Communication"
For i = 2 To Cells(Rows.Count, k).End(xlUp).Row
If Cells(i, k) <> "" Then
str1 = Cells(i, k)
.Bookmarks("Writing_Class1").Range.Text = Cells(i, k) & "(" & Cells(i, "G") & ")"
Exit For
End If
Next i
For i = i + 1 To Cells(Rows.Count, k).End(xlUp).Row
If Cells(i, k) <> "" Then
str1 = Cells(i, k)
.Bookmarks("Writing_Class2").Range.Text = Cells(i, k) & "(" & Cells(i, "G") & ")"
Exit For
End If
Next i
Case "Oral Communication"
For i = 2 To Cells(Rows.Count, k).End(xlUp).Row
If Cells(i, k) <> "" Then
str1 = Cells(i, k)
.Bookmarks("Oral_Class1").Range.Text = Cells(i, k) & "(" & Cells(i, "G") & ")"
Exit For
End If
Next i
For i = i + 1 To Cells(Rows.Count, k).End(xlUp).Row
If Cells(i, k) <> "" Then
str1 = Cells(i, k)
.Bookmarks("Oral_Class2").Range.Text = Cells(i, k) & "(" & Cells(i, "G") & ")"
Exit For
End If
Next i
For i = i + 1 To Cells(Rows.Count, k).End(xlUp).Row
If Cells(i, k) <> "" Then
str1 = Cells(i, k)
.Bookmarks("Oral_Class3").Range.Text = Cells(i, k) & "(" & Cells(i, "G") & ")"
Exit For
End If
Next i
Case "Natural Sciences"
For i = 2 To Cells(Rows.Count, k).End(xlUp).Row
If Cells(i, k) <> "" Then
str1 = Cells(i, k)
.Bookmarks("Science_Class1").Range.Text = Cells(i, k) & "(" & Cells(i, "G") & ")"
Exit For
End If
Next i
For i = i + 1 To Cells(Rows.Count, k).End(xlUp).Row
If Cells(i, k) <> "" Then
str1 = Cells(i, k)
.Bookmarks("Science_Class2").Range.Text = Cells(i, k) & "(" & Cells(i, "G") & ")"
Exit For
End If
Next i
For i = i + 1 To Cells(Rows.Count, k).End(xlUp).Row
If Cells(i, k) <> "" Then
str1 = Cells(i, k)
.Bookmarks("Science_Class3").Range.Text = Cells(i, k) & "(" & Cells(i, "G") & ")"
Exit For
End If
Next i
For i = i + 1 To Cells(Rows.Count, k).End(xlUp).Row
If Cells(i, k) <> "" Then
str1 = Cells(i, k)
.Bookmarks("Science_Class4").Range.Text = Cells(i, k) & "(" & Cells(i, "G") & ")"
Exit For
End If
Next i
Case "Foreign Languages"
For i = 2 To Cells(Rows.Count, k).End(xlUp).Row
If Cells(i, k) <> "" Then
str1 = Cells(i, k)
.Bookmarks("Foreign_Class1").Range.Text = Cells(i, k) & "(" & Cells(i, "G") & ")"
Exit For
End If
Next i
For i = i + 1 To Cells(Rows.Count, k).End(xlUp).Row
If Cells(i, k) <> "" Then
str1 = Cells(i, k)
.Bookmarks("Foreign_Class2").Range.Text = Cells(i, k) & "(" & Cells(i, "G") & ")"
Exit For
End If
Next i
For i = i + 1 To Cells(Rows.Count, k).End(xlUp).Row
If Cells(i, k) <> "" Then
str1 = Cells(i, k)
.Bookmarks("Foreign_Class3").Range.Text = Cells(i, k) & "(" & Cells(i, "G") & ")"
Exit For
End If
Next i
For i = i + 1 To Cells(Rows.Count, k).End(xlUp).Row
If Cells(i, k) <> "" Then
str1 = Cells(i, k)
.Bookmarks("Foreign_Class4").Range.Text = Cells(i, k) & "(" & Cells(i, "G") & ")"
Exit For
End If
Next i
Case "Heritage"
For i = 2 To Cells(Rows.Count, k).End(xlUp).Row
If Cells(i, k) <> "" Then
str1 = Cells(i, k)
.Bookmarks("Heritage_Class1").Range.Text = Cells(i, k) & "(" & Cells(i, "G") & ")"
Exit For
End If
Next i
For i = i + 1 To Cells(Rows.Count, k).End(xlUp).Row
If Cells(i, k) <> "" Then
str1 = Cells(i, k)
.Bookmarks("Heritage_Class2").Range.Text = Cells(i, k) & "(" & Cells(i, "G") & ")"
Exit For
End If
Next i
For i = i + 1 To Cells(Rows.Count, k).End(xlUp).Row
If Cells(i, k) <> "" Then
str1 = Cells(i, k)
.Bookmarks("Heritage_Class3").Range.Text = Cells(i, k) & "(" & Cells(i, "G") & ")"
Exit For
End If
Next i
For i = i + 1 To Cells(Rows.Count, k).End(xlUp).Row
If Cells(i, k) <> "" Then
str1 = Cells(i, k)
.Bookmarks("Heritage_Class4").Range.Text = Cells(i, k) & "(" & Cells(i, "G") & ")"
Exit For
End If
Next i
For i = i + 1 To Cells(Rows.Count, k).End(xlUp).Row
If Cells(i, k) <> "" Then
str1 = Cells(i, k)
.Bookmarks("Heritage_Class5").Range.Text = Cells(i, k) & "(" & Cells(i, "G") & ")"
Exit For
End If
Next i
Case "Humanities"
For i = 2 To Cells(Rows.Count, k).End(xlUp).Row
If Cells(i, k) <> "" Then
str1 = Cells(i, k)
.Bookmarks("Humanities_Class1").Range.Text = Cells(i, k) & "(" & Cells(i, "G") & ")"
Exit For
End If
Next i
For i = i + 1 To Cells(Rows.Count, k).End(xlUp).Row
If Cells(i, k) <> "" Then
str1 = Cells(i, k)
.Bookmarks("Humanities_Class2").Range.Text = Cells(i, k) & "(" & Cells(i, "G") & ")"
Exit For
End If
Next i
For i = i + 1 To Cells(Rows.Count, k).End(xlUp).Row
If Cells(i, k) <> "" Then
str1 = Cells(i, k)
.Bookmarks("Humanities_Class3").Range.Text = Cells(i, k) & "(" & Cells(i, "G") & ")"
Exit For
End If
Next i
For i = i + 1 To Cells(Rows.Count, k).End(xlUp).Row
If Cells(i, k) <> "" Then
str1 = Cells(i, k)
.Bookmarks("Humanities_Class4").Range.Text = Cells(i, k) & "(" & Cells(i, "G") & ")"
Exit For
End If
Next i
For i = i + 1 To Cells(Rows.Count, k).End(xlUp).Row
If Cells(i, k) <> "" Then
str1 = Cells(i, k)
.Bookmarks("Humanities_Class5").Range.Text = Cells(i, k) & "(" & Cells(i, "G") & ")"
Exit For
End If
Next i
Case "Social & Behavioral Sciences"
For i = 2 To Cells(Rows.Count, k).End(xlUp).Row
If Cells(i, k) <> "" Then
str1 = Cells(i, k)
.Bookmarks("Social_Class1").Range.Text = Cells(i, k) & "(" & Cells(i, "G") & ")"
Exit For
End If
Next i
For i = i + 1 To Cells(Rows.Count, k).End(xlUp).Row
If Cells(i, k) <> "" Then
str1 = Cells(i, k)
.Bookmarks("Social_Class2").Range.Text = Cells(i, k) & "(" & Cells(i, "G") & ")"
Exit For
End If
Next i
For i = i + 1 To Cells(Rows.Count, k).End(xlUp).Row
If Cells(i, k) <> "" Then
str1 = Cells(i, k)
.Bookmarks("Social_Class3").Range.Text = Cells(i, k) & "(" & Cells(i, "G") & ")"
Exit For
End If
Next i
For i = i + 1 To Cells(Rows.Count, k).End(xlUp).Row
If Cells(i, k) <> "" Then
str1 = Cells(i, k)
.Bookmarks("Social_Class4").Range.Text = Cells(i, k) & "(" & Cells(i, "G") & ")"
Exit For
End If
Next i
For i = i + 1 To Cells(Rows.Count, k).End(xlUp).Row
If Cells(i, k) <> "" Then
str1 = Cells(i, k)
.Bookmarks("Social_Class5").Range.Text = Cells(i, k) & "(" & Cells(i, "G") & ")"
Exit For
End If
Next i
For i = i + 1 To Cells(Rows.Count, k).End(xlUp).Row
If Cells(i, k) <> "" Then
str1 = Cells(i, k)
.Bookmarks("Social_Class6").Range.Text = Cells(i, k) & "(" & Cells(i, "G") & ")"
Exit For
End If
Next i
For i = i + 1 To Cells(Rows.Count, k).End(xlUp).Row
If Cells(i, k) <> "" Then
str1 = Cells(i, k)
.Bookmarks("Social_Class7").Range.Text = Cells(i, k) & "(" & Cells(i, "G") & ")"
Exit For
End If
Next i
Case "Quantitative Reasoning"
For i = 2 To Cells(Rows.Count, k).End(xlUp).Row
If Cells(i, k) <> "" Then
str1 = Cells(i, k)
.Bookmarks("Quantitative_Class1").Range.Text = Cells(i, k) & "(" & Cells(i, "G") & ")"
Exit For
End If
Next i
For i = i + 1 To Cells(Rows.Count, k).End(xlUp).Row
If Cells(i, k) <> "" Then
str1 = Cells(i, k)
.Bookmarks("Quantitative_Class2").Range.Text = Cells(i, k) & "(" & Cells(i, "G") & ")"
Exit For
End If
Next i
For i = i + 1 To Cells(Rows.Count, k).End(xlUp).Row
If Cells(i, k) <> "" Then
str1 = Cells(i, k)
.Bookmarks("Quantitative_Class3").Range.Text = Cells(i, k) & "(" & Cells(i, "G") & ")"
Exit For
End If
Next i
Case "Computer Literacy"
For i = 2 To Cells(Rows.Count, k).End(xlUp).Row
If Cells(i, k) <> "" Then
str1 = Cells(i, k)
.Bookmarks("Computer_Class1").Range.Text = Cells(i, k) & "(" & Cells(i, "G") & ")"
Exit For
End If
Next i
For i = i + 1 To Cells(Rows.Count, k).End(xlUp).Row
If Cells(i, k) <> "" Then
str1 = Cells(i, k)
.Bookmarks("Computer_Class1").Range.Text = Cells("'Core Category'!I3") & "(" & ("CIS 212/INF 104") & Cells("'Core Category'!I10") & ("CIS 212/INF 104/TEC 161") & ")"
Exit For
End If
Next i
End Select
Next k
.SaveAs ThisWorkbook.Path & "\" & ws.Name & ".docx"
.Close
End With
Set objDoc = Nothing
End If