I have a macro that opens a word template, puts data from an excel worksheet into different bookmarks in word, saves, closes and repeats with next worksheet. All of that works fine, just giving some background.
The problem I am having is with a piece of code inside that.
I want this specific piece of code to look through the current worksheet, see that no headers say Computer Literacy and then do this code. The code is supposed to put the data from I3 on the worksheet called Core Category into a bookmark, and then the rest. Hopefully that is enough explanation for someone to get me where I need to go.
I know I don't have my syntax right, so please help.
Thank you.
Full macro below:
The problem I am having is with a piece of code inside that.
Code:
If Application.CountIf(Range("I1:Q1"), "Computer Literacy") = 0 Then
.Bookmarks("Computer_Class1").Range.Text = ('Core Category'!"I3") & "(" & ("CIS 212/INF 104") & ('Core Category'!"I10") & ("CIS 212/INF 104/TEC 161") & ")"
End IfI want this specific piece of code to look through the current worksheet, see that no headers say Computer Literacy and then do this code. The code is supposed to put the data from I3 on the worksheet called Core Category into a bookmark, and then the rest. Hopefully that is enough explanation for someone to get me where I need to go.
I know I don't have my syntax right, so please help.
Thank you.
Full macro below:
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\thistemplate.dotm") ' change as required
With objDoc
.Bookmarks("EKU_Major").Range.Text = ws.Name ' puts wksht name as the major
If Application.CountIf(Range("I1:Q1"), "Computer Literacy") = 0 Then
.Bookmarks("Computer_Class1").Range.Text = Cells('Core Category'!"I3") & "(" & ("CIS 212/INF 104") & Cells('Core Category'!"I10") & ("CIS 212/INF 104/TEC 161") & ")"
End If
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
Next ws
objWord.Quit
Set objWord = Nothing
End Sub
