how to build a dynamic array and search it for a value

M

michael.beckinsale

Hi All,

What l want to do is to copy rows from 'source' workbooks and append
to a 'master' workbook but only rows where the unique reference does
not already exist in the 'master' workbook. The source & master
workbooks are identical in layout and the unique identifier is in
column A.

I have used the code generously given by Ron De Bruin to loop through
all the source workbooks and all works fine but l am stumped as to the
best way to achieve the above efficiently without going down the
Access / SQL route.

My thoughts were to build an array from column A of the 'master'
workbook and then loop through each row of column A of the 'source'
workbook copying the data if the cell(r,"A").Value was in the array.

Can anybody point me in the right direction with some sample code to:

1) Build the 'master' workbook array
2) Check that the current 'source' record idnetifier exists in the
array

Alternatively suggest a better / quicker method with some example
code. There is likely to be approx 12 workbooks with 300 rows /
records in each. VBA code needs to be compatible with XL2003 & XL2007.

TIA

All help suggestions greatly appreciated.

Regards

Michael
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Cell values to an array, then array transpose to another workbook 8
Loop through named workbooks 1
Use a master workbook to carry over changes to multiple workbooks 1
VBA code for pasting links -- modifying RDB code for summary workbook 11
Ignoring a runtime error 1
Search an array of sheets for an array of numbers & return count of numbers 11
Copy range from multiple files in multiple folders to single sheet in master WB 10
populate a combobox based on unique values in another workbook 16

Top