matching data in VBA
 

Hi

I have two columns in the 6th sheet of a workbook. This sheet's name
changes every time the macro is run e.g. Now it is Sheet12 but it could
be Sheet13 next time I run the macro.

One column is generated via a pivot table - starting cell ref =a5. The
other is from a filter source data list and copy pasted - starting cell
ref = e5.

I need to match the codes and where the pivot table does not show the
codes listed in column e, I need to identify these codes and extract
them out into a new column.

I then need to go back to the source workbook and copy all the rows
that match those codes.

column a
A&L002 1
AAL002 1
AAL003 1
ABS002 1
ADA001 1
AEY369 1
AEY370 1
AEY371 1
AEY372 1
AEY373 1
AEY374 1

column e
A&L002
ABS002
AEY369
AEY371
AEY372
AEY373
AEY374
AEY376
AEY377
AEY378
AEY379

These columns may be different lengths according to how many new codes
there are each week AND the number of codes may change so the ranges
need to be dynamic also. The number beside the first column (pivot
data) is just how i determine whether the old code is current.

I am having trouble in VBA working out this code. Can anyone please
assist?


---
Message posted from http://www.ExcelForum.com/

matching data in VBA
 

I have found two formulas in excel which will do the first part of thi
but am not sure how to convert them to vba and then do the find, cop
from one workbook and paste into the second.

=IF(COUNTIF($A$1:$A$10,B1)=0,B1,"") which extracts and then

=IF(ROW()-ROW(NoBlanksRange)+1ROWS(BlanksRange)-
COUNTBLANK(BlanksRange),"",INDIRECT(ADDRESS(SMALL(
(IF(BlanksRange<"",ROW(BlanksRange),ROW()+ROWS(Bl anksRange))),
ROW()-ROW(NoBlanksRange)+1),COLUMN(BlanksRange),4)))

(these are from http://www.cpearson.com/excel/noblanks.htm to give th
credit:))

can anyone please assist?

cheers

rebecc

--
Message posted from http://www.ExcelForum.com