Pasting in word table, below bookmarks

[klutch]

Hello, I am very close to completing a macro I've been working on for about two weeks. This is what I've aimed to accomplish:
1. Select a specific cell in excel correlating to specific text in column A (the length of my data rows will vary and I need to copy column E of the last row)
2. Select a word file from libraries (different files for each documentation)
3. Paste into word doc table in relating categories. (Pulling results for 3 different runs)

Here is my code

 Sub CopyAndPaste()
Dim myfile, wdApp As New Word.Application, wdDoc As Word.Document
myfile = Application.GetOpenFilename(, , "Browse for Document")
Dim i As Integer
i = Application.Match("Avg", Sheet1.Range("A1:A20"), 0)
Range("E" & i).Select
Selection.Copy
 
wdApp.Visible = True
Set wdDoc = wdApp.Documents.Open(myfile)
'select the word range you want to paste into
  If Range("c2") = 22 Then wdDoc.Bookmarks("d22").Select
  If Range("c2") = 5 Then wdDoc.Bookmarks("d5").Select
  If Range("c2") = -20 Then wdDoc.Bookmarks("d20").Select
 
 
 
    'and paste the clipboard contents
    wdApp.Selection.Paste
End Sub

This code accomplishes my first two objectives, and partially the third.
As you can see, I have used bookmarks to paste where I want to in the doc. However, this table will be updated every 18 months per review standard and the people using it have no VBA experience(they wont know how to change the bookmarks in code). This means that the text being pasted into the current bookmarks will be replaced with the new data. This is no good for what I want to accomplish with this macro.



is there a way to code so that if the bookmark already has text in it, the data will be pasted below it?

I know this is more of an excel problem but I have been told several times that my best luck finding an answer to this question would be in a word forum.

If anyone has anything to add to this I would be very appreciative!!

[d4okeefe]

You can access the Range object from a bookmark.

Sub add_text_to_bookmark()
    Dim bk As Bookmark
    For Each bk In ActiveDocument.Bookmarks
        Dim r As Range
        Set r = bk.Range
        r.InsertAfter = "Insert here"
    Next
End Sub

Also, see this link. Toward the end there's an explanation for expanding the range of the bookmark. Essentially, to do this, you have to delete the original bookmark and recreate it with a new range.

[klutch]

Sub CopyAndPaste()
Dim myfile, wdApp As New Word.Application, wdDoc As Word.Document
myfile = Application.GetOpenFilename(, , "Browse for Document")
Dim i As Integer
i = Application.Match("Avg", Sheet1.Range("A1:A20"), 0)
Range("E" & i).Select
Selection.Copy

wdApp.Visible = True
Set wdDoc = wdApp.Documents.Open(myfile)
'select the word range you want to paste into
  If Range("c2") = 22 Then wdDoc.Bookmarks("d22").Select
 Dim bk As Bookmark
    For Each bk In ActiveDocument.Bookmarks
        Dim r As Range
        Set r = bk.Range
        r.InsertAfter = "below"
    Next
  If Range("c2") = 5 Then wdDoc.Bookmarks("d5").Select
   For Each bk In ActiveDocument.Bookmarks
        
        r.InsertAfter = "below"
    Next
  
  If Range("c2") = -20 Then wdDoc.Bookmarks("d20").Select
   For Each bk In ActiveDocument.Bookmarks
    
        
        r.InsertAfter = "below"
    Next
  
    
    
    'and paste the clipboard contents
    wdApp.Selection.Paste
End Sub

Here is how I tried to implement. It is still just replacing the bookmark instead of pasting it below

[d4okeefe]

Sorry, I think I made that confusing. There's no need for the for loop in your code.
Do something like this:

Sub CopyAndPaste()
    Dim myfile, wdApp As New Word.Application, wdDoc As Word.Document
    myfile = Application.GetOpenFilename(, , "Browse for Document")
    Dim i As Integer
    i = Application.Match("Avg", Sheet1.Range("A1:A20"), 0)
    Range("E" & i).Select
    Selection.Copy

    wdApp.Visible = True
    Set wdDoc = wdApp.Documents.Open(myfile)
    'select the word range you want to paste into
    If Range("c2") = 22 Then
         wdDoc.Bookmarks("d22").Select
         Selection.Collapse wdCollapseEnd
         Selection.Paste
    End If
    If Range("c2") = 5 Then
         wdDoc.Bookmarks("d5").Select
         Selection.Collapse wdCollapseEnd
         Selection.Paste
    End If
    If Range("c2") = -20 Then 
         wdDoc.Bookmarks("d20").Select
         Selection.Collapse wdCollapseEnd
         Selection.Paste
    End If
End Sub

[klutch]

For some reason I am getting a compile error " End if without Block If" on the EndIf before the d5 bookmark line, does this happen for you?

[d4okeefe]

Try it again. There was an issue with each of the if statements.

[klutch]

I copied the code to a different worksheet and it had the same error

[d4okeefe]

I edited the last block of code I posted. I think it should be ok now.

Basically, if you go back to the original code you posted, rather than using ...

wdApp.Selection.Paste

... you should type this ...

wdApp.Selection.Collapse wdCollapseEnd
wdApp.Selection.Paste

Then it should work.

[klutch]

Like this?

Sub CopyAndPaste()
    Dim myfile, wdApp As New Word.Application, wdDoc As Word.Document
    myfile = Application.GetOpenFilename(, , "Browse for Document")
    Dim i As Integer
    i = Application.Match("Avg", Sheet1.Range("A1:A20"), 0)
    Range("E" & i).Select
    Selection.Copy
    wdApp.Visible = True
    Set wdDoc = wdApp.Documents.Open(myfile)
    'select the word range you want to paste into
    Dim r As Range
    If Range("c2") = 22 Then wdDoc.Bookmarks("d22").Select
         Set r = Selection.Range
         r.Collapse wdCollapseEnd
         wdApp.Selection.Collapse wdCollapseEnd
wdApp.Selection.Paste
    End If
     If Range("c2") = 5 Then wdDoc.Bookmarks("d5").Select
         Set r = Selection.Range
         r.Collapse wdCollapseEnd
         wdApp.Selection.Collapse wdCollapseEnd
wdApp.Selection.Paste
   End If
    If Range("c2") = -20 Then wdDoc.Bookmarks("d20").Select
         Set r = Selection.Range
         r.Collapse wdCollapseEnd
         
         wdApp.Selection.Collapse wdCollapseEnd
wdApp.Selection.Paste
    End If
End Sub

still getting error :/

[klutch]

I copied your edited code and got error '438' object doesn't support this property or method on line

 Selection.Collapse wdCollapseEnd

under the d5 bookmark

[d4okeefe]

I was thinking more like this.

Sub CopyAndPaste()
    Dim myfile, wdApp As New Word.Application, wdDoc As Word.Document
    myfile = Application.GetOpenFilename(, , "Browse for Document")
    Dim i As Integer
    i = Application.Match("Avg", Sheet1.Range("A1:A20"), 0)
    Range("E" & i).Select
    Selection.Copy
 
    wdApp.Visible = True
    Set wdDoc = wdApp.Documents.Open(myfile)
    'select the word range you want to paste into
    If Range("c2") = 22 Then wdDoc.Bookmarks("d22").Select
    If Range("c2") = 5 Then wdDoc.Bookmarks("d5").Select
    If Range("c2") = -20 Then wdDoc.Bookmarks("d20").Select
    
    'and paste the clipboard contents
    wdApp.Selection.Collapse wdCollapseEnd
    wdApp.Selection.Paste
End Sub

You don't need the range object that I mentioned earlier. You could use it to change the beginning and end of the bookmark, but that seems needlessly complicated now.

[klutch]

Okay this runs now but it does essentially the same as my original code. It still replaces the previous text in the bookmark instead of pasting below it. Is there a way I can post an image of it so you can see what I am trying to do?

[d4okeefe]

Sure. You want to add text below the current bookmarked text, correct?

Essentially, here's how I read this code: After capturing a value from a spreadsheet, this code opens a Word file then, if a match is found in 1 of 3 different places (1) select the text where the bookmark is, (2) places the cursor at the end of the selection, and finally (3) pastes the value from the clipboard.

[klutch]

In a sense yes that is what my goal was. My previous code would paste into the bookmark that I had within the table. What I am now seeking to do now is put in a line of code to paste the data into the next blank space down the column of the table. This macro is for performance reviews. That being said, there is a new review after a set period of time. The table that is being pasted into will fill up over time, meaning that the pasting into the bookmark is okay for the first time but I do not want to replace the data, I want it to paste into the next blank cell (within the column of the table) if there is text in the bookmark and following cells.

I will be running this macro on the same document every 18 months

[klutch]

How do I post a picture of my table? I have screenshots of it