Find Cell Based On Last Copied Text In Word

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.

Re: Find A Cell Based On Last Copied Text In Word

I checked that out but still can't get it. Code now looks like:

Sub LookupZip()
    Dim appExcel As Excel.Application
    Dim ZipCopy As Object
    Set ZipCopy = Selection.Range
    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:=ZipCopy, After:=ActiveCell, LookIn:=xlFormulas, LookAt _
    :=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:= _
    False, SearchFormat:=False).Activate
    ActiveCell.Offset(0, 8).Range("A1").Copy
    
    
End Sub

The "What:" value is showing up as "" when it should show the zip code I just copied from the Word doc. Not sure where I'm going wrong.

Re: Find A Cell Based On Last Copied Text In Word

Thanks. I get an missing object error. I'm going to try to use an input box to pop up, I'll paste the zip into that and have the code find the value of that box. Maybe that will work.

Re: Find A Cell Based On Last Copied Text In Word

Compile Error: Object Required is what I get. I tried the Input Box and it works but only if I run the macro seperately from the macro that sets up the Word doc. It's wierd. At the end of the Setup Macro I call the macro that opens the Excel workbook and does the search for the zip code and it fails to work correctly. I may just have to do this as a two-step process using two macro buttons on my toolbar.