On May 30, 9:21*pm, Mike H. wrote:
Here is one way:
Option Explicit
Option Base 1

Sub UpdateSheet1()
Dim DataArray(65000, 24) As Variant
Dim Fnd As Double
Dim X As Double
Dim Y As Double
Dim Z As Double
Dim LookupRng As Range
Dim Res As Variant

Sheets("Sheet1").Select
Set LookupRng = Workbooks("wb containing sheet1.xls").Names("NamedRange-All
Data").RefersToRange

Do While True
* * If Cells(X, 1).Value = Empty Then Exit Do
* * Res = Application.VLookup(Cells(X, 4), LookupRng, 4, False)
* * 'lookup up col d in sheet1. *If not found there is an error so pick up
all data from this row to add
* * If Not (IsError(Res)) Then
* * Else
* * * * Fnd = Fnd + 1
* * * * For Y = 1 To 26
* * * * * * DataArray(Fnd, Y) = Cells(X, Y).Value
* * * * Next
* * End If
* * X = X + 1
Loop

Windows("wb containing sheet1.xls").Activate
Sheets("sheet1").Select
Range("A65000").End(xlUp).Select *'this is a row with data, this row +1 is
empty!
X = ActiveCell.Row + 1
For Y = 1 To Fnd *'this will populate all your "new" data to sheet1...
* * For Z = 1 To 24
* * * * Cells(X, Z).Value = DataArray(Y, Z)
* * Next
* * X = X + 1
Next

End Sub




"If Cells(X, 1).Value = Empty Then Exit Do" This line makes an error
of the followings

' Run-time error '1004', application defined or object defined error'