Hi all,
I am quite new to VBA, and am struggling somewhat with this. Essentially I require a macro that can pull data from Excel and paste into a bookmark in Word. I need it to search Excel for a word which has been inputted by the user into a fillable form, and if there is a match copy the value of another cell (e.g. 5 columns across) and paste into the bookmark. I have come up with the below code, which needs some fixing. Any help would be very greatly appreciated.
Cheers,
James
Code:
Sub CallEx3()
Dim objExcel As New Excel.Application
Dim exWb As Excel.Workbook
Dim oRng As Excel.Range
Dim oSheet As Excel.Worksheet
Dim LastRow As Long
Set exWb = objExcel.Workbooks.Open("U:\VBA Word Automation\Excel Test Sheet.xlsx")
For Each oSheet In objExcel.ActiveWorkbook.Worksheets
LastRow = Cells(Rows.Count, 1).End(xlUp).Row
For i = 2 To LastRow
ThisValue = Cells(i, 1).Value
If ThisValue = TextBox1.Text Then
Cells(i, 6).Copy
WordApp.Selection.PasteSpecial.FillBM Link:=False, DataType:=wdPasteText, _
Placement:="bmDT", DisplayAsIcon:=False
End If
Next i
Next oSheet
exWb.Close
Set exWb = Nothing
Set objExcel = Nothing
Set oRng = Nothing
Exit Sub
End Sub