Why isn't this working?

S

Sethomas5

Board Regular
Joined
Oct 5, 2015
Messages
204
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
 

Excel Facts

How to fill five years of quarters?
Type 1Q-2023 in a cell. Grab the fill handle and drag down or right. After 4Q-2023, Excel will jump to 1Q-2024. Dash can be any character.
Sort by date Sort by votes
You need to fully qualify ALL the Cells statements.

Because you're iterating through all the worksheets, the active worksheet is where the data is coming from; not the one in the loop.

Code: 
For i = 2 To Cells(Rows.Count, k).End(xlUp).Row
Needs to be
Code: 
For i = 2 To WS.Cells(Rows.Count, k).End(xlUp).Row
all the way through the code.
 
Upvote 0
Tinbendr said:
You need to fully qualify ALL the Cells statements.

Because you're iterating through all the worksheets, the active worksheet is where the data is coming from; not the one in the loop.

Code: 
For i = 2 To Cells(Rows.Count, k).End(xlUp).Row
Needs to be
Code: 
For i = 2 To WS.Cells(Rows.Count, k).End(xlUp).Row
all the way through the code.
Click to expand...

That makes SO much sense. Let me give that whirl and see how things go.

Thank you so much for your reply.
 
Upvote 0
Tinbendr said:
You need to fully qualify ALL the Cells statements.

Because you're iterating through all the worksheets, the active worksheet is where the data is coming from; not the one in the loop.

Code: 
For i = 2 To Cells(Rows.Count, k).End(xlUp).Row
Needs to be
Code: 
For i = 2 To WS.Cells(Rows.Count, k).End(xlUp).Row
all the way through the code.
Click to expand...

That worked! Thank you.
Now I have something else I'm having trouble with. I want the code to look for a column header, and if it is not there take information from another worksheet (not active) and put it in the bookmark. This what I have:
Code: 
Case Is <> "Computer Literacy"
                        For i = 2 To ws.Cells(Rows.Count, k).End(xlUp).Row
                            If ws.Cells(i, k) <> "" Then
                            str1 = Cells(i, k)
                                .Bookmarks("Computer_Class1").Range.Text = ws.Cells("'Core Category'!I3") & "(" & ("CIS 212/INF 104") & ws.Cells("'Core Category'!I10") & ("CIS 212/INF 104/TEC 161") & ")"
                        Exit For

I don't know if I'm even anywhere close with that.
 
Upvote 0
Sethomas5 said:
That worked! Thank you.
Now I have something else I'm having trouble with. I want the code to look for a column header, and if it is not there take information from another worksheet (not active) and put it in the bookmark. This what I have:
Click to expand...

I think this needs to be Case Else.
(If nothing else matches the other Cases, then do this.)

Now, tell me how you're going to derive a category from the column list? Is it going to be the next non-empty vale in the column?
 
Upvote 0
Tinbendr said:
I think this needs to be Case Else.
(If nothing else matches the other Cases, then do this.)

Now, tell me how you're going to derive a category from the column list? Is it going to be the next non-empty vale in the column?
Click to expand...

Gotcha.
Sorry, I don't understand the second part?
 
Upvote 0
So, there is no category in the Select Case ws.Cells(1, k).Value that matches. You want to lookup this in another worksheet?
I want the code to look for a column header, and if it is not there take information from another worksheet
Click to expand...
How are you going to correlate ws.Cells(1, k).Value with the look up?
 
Upvote 0
Tinbendr said:
So, there is no category in the Select Case ws.Cells(1, k).Value that matches. You want to lookup this in another worksheet?How are you going to correlate ws.Cells(1, k).Value with the look up?
Click to expand...

I really have no idea...
That's my problem.

Code: 
Case Is <> "Computer Literacy"
                     .Bookmarks("Computer_Class1").Range.Text = ws.Cells("'Core Category'!I3") & "(" & ("CIS 212/INF 104") & ws.Cells("'Core Category'!I10") & ("CIS 212/INF 104/TEC 161") & ")"

I'm thinking that would be closer to what I need...
 
Upvote 0
You must log in or register to reply here.

Similar threads

P
  • Question
Copy an excel table and paste it into word
Replies
2
Views
759
Paskie_EU
P
G
  • Question
VBA code for PowerPoint works when I step through it, but breaks when run
Replies
2
Views
262
Green_Square_Window
G
J
  • Question
CHOOSECOLS Excel 365 function compatible for versions of excel 2007 and newer
Replies
1
Views
631
daniel0929
D
J
  • Question
ARRAYTOTEXT Excel 365 function compatible for versions of excel 2007 and newer
Replies
0
Views
281
johnnyL
J
J
  • Question
Find all or certain formulas in a Workbook
Replies
1
Views
370
johnnyL
J

Forum statistics

Threads
1,214,827
Messages
6,121,818
Members
449,049
Latest member
cybersurfer5000

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top