What is wrong with this code?!?
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").RefersToRan ge 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 |
What is wrong with this code?!?
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. "James8309" wrote: 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").RefersToRan ge 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 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 |
All times are GMT +1. The time now is 03:43 AM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com