I have a macro in Word that copies a string of text (a zip code). I have been manually opening an Excel workbook that contains a column of zip codes. I 'find' the zip code in the list and then tab over eight columns to copy the cell in that row containing the name of the salesman that handles that zip code. I then go back to the Word document and paste that data at the top of the document. I want to automate that part of the task.
I have the following code:
Sub LookupZip() Dim appExcel As Excel.Application Set appExcel = CreateObject("Excel.Application") appExcel.Workbooks.Open ("F:\My Documents\copy of Dallas-Sherman Territories.xls") appExcel.Visible = True appExcel.Worksheets("Zip Code List").Range("A2").Select Cells.Find(What:='Item from the Clipboard' , After:=ActiveCell, LookIn:=xlFormulas, LookAt _ :=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:= _ False, SearchFormat:=False).Activate ActiveCell.Offset(0, 8).Range("A1").Copy appExcel.Quit End Sub
I just need to know to know how to get the item from the clipboard into the Cells.Find code
Any ideas? Thanks.