Filtrar datos de otro libro de excel que se refleje en un Combobox

[Giancarloch]

Buenas tardes comunidad,

Actualmente tengo este problema que he estado desarrollado en un formulario desde varios aporte que he visto, pero que no he podido solucionar este caso, necesito filtrar la informacion de dos datos especificos que se encuentran en otro Libro excel y traer uno, 

Ejemplo.

Código de despacho - Descripción  - Lote

001                                      Zanahorias          9121

001                                       Manzana             9121

002                                      Peras                      9121

003                                      Cebollas               8090

003                                      Tomates                8090

004                                       Patatas                 7081

005                                      Limones                 9121

Necesito que se me refleje el Código de despacho en el combo box el 001, 002 y 005 pero filtrado con un lote en especifico el cual es el 9121 y ubicar el Codigo de despacho en una casilla especifica en mi excel

 

Dejo codigo que estado desarrollando, pero solo me refleja los codigos de despacho de todos los lotes.

 

Private Sub CommandButton1_Click()
Unload Me
End Sub
Private Sub CommandButton2_Click()
Dim HojaDestino As Range
Hoja1.Range("F5") = ComboBox1
Unload Me
End Sub

Private Sub UserForm_Initialize()
Dim sd As New Collection
Dim celda As Range
Dim dato
Dim r As String
Dim uf As Integer
Dim Lote As String
Lote = "9121"

Application.ScreenUpdating = False
On Error Resume Next
ComboBox1.Clear
Dim myfile As String
myfile = Application.GetOpenFilename("Archivos Excel (*.xl*), *.xl*")
Workbooks.Open Filename:=myfile, UpdateLinks:=0
Sheets(1).Range("O2").AutoFilter Field:=15, Criteria1:=Lote
Sheets(1).Range("J2").Select
uf = Range("J" & Rows.Count).End(xlUp).Row
r = "J2:J" & uf
For Each celda In Range(r)
sd.Add celda.Value, CStr(celda.Value)
Next celda
For Each dato In sd
ComboBox1.AddItem dato
Next dato
Application.ScreenUpdating = True

End Sub

[JasallBcn]

Buenas,  una manera seria comprobar la columna lote .

For Each celda In Range(r)
 If celda.Offset(0, 5).Value = Val(Lote) Then
  sd.Add celda.Value, CStr(celda.Value)
 End If
Next celda

saludos

[Giancarloch]

@JasallBcn muchas gracias nuevamente, ejecute el comando como lo dices y todo salio a la perfeccion, agradezco por dedicar tu tiempo.

Tengo otra duda, si deseo cerrar el archivo excel que abri en este codigo, como lo deberia hacer apenas seleccione el codigo del despacho?

Muchas gracias,

Saludos