VLOOKUP from closed sheet

[MACY8167]

The below Macro is used to find a value from a closed sheet and return it to the open sheet. Whe the "closed sheet" is closed the lookup can find some but not all of the values. If the "closed" sheet is opened all of the values are returned. I am really curious as to why this happens




Rows("1:1").Select
Selection.Insert Shift:=xlDown
Range("A1").Select
ActiveCell.FormulaR1C1 = "Invoice #"
Range("B1").Select
ActiveCell.FormulaR1C1 = "Contact"
Range("C1").Select
ActiveCell.FormulaR1C1 = "Date"
Range("D1").Select
ActiveCell.FormulaR1C1 = "VPO #"
Range("E1").Select
ActiveCell.FormulaR1C1 = "Customer #"
Range("F1").Select
ActiveCell.FormulaR1C1 = "Batch"
Range("B2").Select
ActiveWorkbook.Names.Add Name:="Contact", RefersToR1C1:= _
"=[formpref.xls]Sheet1!C1:C4"

ActiveCell.FormulaR1C1 = "=VLOOKUP(RC[2],contact,2,0)"
Range("B2").Select
Selection.Copy
[B2].Copy Range("B3", Range("A65536").End(xlUp).Offset(, 1))
Range("A2").Select
Application.CutCopyMode = False
Selection.AutoFilter
Selection.AutoFilter Field:=2, Criteria1:="#N/A"
Range("A6").Select
With ActiveSheet.PageSetup
.PrintGridlines = True
End With
End Sub

Any ideas?