jaedong1221

06-04-2008, 04:35 PM

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.

"Testing" as workbook containing '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)

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

For Z = 1 To 24

Cells(X, Z).Value = DataArray(Y, Z)

Next

X = X + 1

Next

End Sub

thank you!

regards,

James.

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.

"Testing" as workbook containing '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)

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

For Z = 1 To 24

Cells(X, Z).Value = DataArray(Y, Z)

Next

X = X + 1

Next

End Sub

thank you!

regards,

James.