What is wrong with this code?!?

[James8309]

Hi everyone:

1. I have two sheets, "Sheet1" and "Sheet2" containing data and they
are in the same workbook.

2. "Sheet2" have some data from "Sheet1" but also includes different
data as well.

3. I can find new data (different) data in "Sheet2" by using vlookup.
however I have macro running on sheet1 to create pivot table report.
I
am just missing those new data from Sheet2.

4. Both "Sheet1" and "Sheet2" has data from column A to X. Common
lookup column being D. Row numbers change each month.


I got this code at the moment and instead of attaching all the new
data in "Sheet2" to "Sheet1" it just copies all the data in "Sheet1"
and paste right under the bottom in "Sheet1".
Can anybody point out what is wrong with this code?

I defined "Look" as Range of Column("D:X") in Sheet1.

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("Testing.xls").Names("Look").RefersToRange

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

Windows("Testing.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 new data from sheet2 to sheet1
For Z = 1 To 24
Cells(X, Z).Value = DataArray(Y, Z)
Next
X = X + 1
Next


End Sub

 

[Mike H.]

Windows("Testing.xls").Activate

Sheets("Sheet1").Select

change the above two lines to be where you wish the data to go.
Then it should work for you.