Link to home
Start Free TrialLog in
Avatar of riley burle
riley burle

asked on

Searching Column A for text, then copying Cell in Column E of same row.

I have a similar scenario. I am searching column A for the text "Avg" and then I need to copy the cell in Column E of the same row that the text was found. Then I am pasting it into a word table ( also having issues with placing the paste INSIDE the table).
 Sub CopyAndPaste()
Dim myfile, wdApp As New Word.Application, wdDoc As Word.Document
myfile = Application.GetOpenFilename(, , "Browse for Document")
Range("e4").Copy
wdApp.Visible = True
Set wdDoc = wdApp.Documents.Open(myfile)
wdDoc.Range.Characters.Last.PasteExcelTable False, True, False
End Sub 

Open in new window

In this code, I have entered the cell that I want copied manually from looking at the sheet. I need this to be automated.
It pastes into the Word doc but underneath the table I want it to go in. Can I bookmark the table and set code to paste it in cell (2,4)?
Avatar of riley burle
riley burle

ASKER

Okay, I solved part of my problem.
Here is how I copy the cell I want
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
    wdDoc.Bookmarks("PRtable").Select
    'and paste the clipboard contents
    wdApp.Selection.Paste

End Sub

Open in new window

Now my problem is pasting into the correct cell of the bookmarked cell in myfile. Anyone have solutions to this?
Avatar of Martin Liss
I don't, but your code could be improved if you didn't use Select, so replace rows 6 and 7 with one row that says

Range("E" & i).Copy
This question needs an answer!
Become an EE member today
7 DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform.
View membership options
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.