Solved: vlookup problem

[wakdafak]

i have a query here

i have 2 workbooks:
workbook one: contain 1 sheet that have airwaybills number.
workbook two: contain 2 sheet that contain airwaybills number and shipper reff.

can sombody help me how can i vlookup the airwaybills and get the shipper reff and paste it into the new workbook.

:banghead:

thanks in advance

[Bob Phillips]

Have you tried it? There is no problem with VLOOKUP across workbooks.

[wakdafak]

it can be done only when i insert the vlookup manually

i need a code of vba to do it...

[Bob Phillips]

So do it in Excel with the macro recorder on, there you have the code.

[wakdafak]

done

thanks for giving me the idea :friends:

:thumb

[wakdafak]

Sub vlookupservicearea()
Dim shtt As Worksheet
Dim wrkk As Workbook
lMaxSht = Sheets.Count
For shtno = 2 To lMaxSht
Set wrkk = ActiveWorkbook
Set shtt = wrkk.Worksheets(shtno)
If Worksheets(shtno).Range("D2").Value Like "9*" Then
For Each shtt In wrkk.Worksheets
If shtt.Name Like "HM*" Then
shtt.Select
Range("X2").Select
ActiveCell.FormulaR1C1 = "=VLOOKUP(C[-7],OPMS!RC[-21]:R[4261]C[58],27,FALSE)"
Selection.AutoFill Destination:=Range("X2:X2000"), Type:=xlFillDefault
Range("X1").Select
ActiveCell.FormulaR1C1 = "Country Code Area"
Columns("X:X").EntireColumn.AutoFit
With Selection.Interior
.ColorIndex = 6
.Pattern = xlSolid
End With
End If
Next
Else
For Each shtt In wrkk.Worksheets
If shtt.Name Like "HM*" Then
shtt.Select
Range("X2").Select
ActiveCell.FormulaR1C1 = "=VLOOKUP(C[-7],OPMS!RC[-21]:R[4261]C[58],32,FALSE)"
Selection.AutoFill Destination:=Range("X2:X2000"), Type:=xlFillDefault
Range("X1").Select
ActiveCell.FormulaR1C1 = "Services Area Code"
Columns("X:X").EntireColumn.AutoFit
With Selection.Interior
.ColorIndex = 6
.Pattern = xlSolid
End With
End If
Next
End If
Next
End Sub


working perfect... :clap: