Find and copy between sheets (workbooks)

The looping approach you used with 42.000 items can take some time to run. Try this macro. If I understood correctly what you want to do, it should be quite fast.

Please Login or Register  to view this content.

This assumes both sheets are in the same workbook. If you want to use Sheet2 from a different workbook, what is the full name of that workbook including extension (xlsx, xlsm)? Are your actual sheet names "Sheet1" and "Sheet2"? If not, what are the actual names?

Hi there,

See if the following code does what you need - when I used it with a "Sheet 2" containing 42,000 items and a "Sheet 1" containing 1,000, it executed in approximately one second:

Please Login or Register  to view this content.

The highlighted values may be altered to suit your requirements.


Hope this helps - please let me know how you get on.

Regards,

Greg M


P. S. When this routine was executed using random numbers with a greater range of values, the execution time increased significantly - in the earlier executions, duplicate random numbers were probably found more quickly if they were located closer to the top of the column being searched.

@Greg M
Glad it was of some help.

Place this macro in the workbook that contains the "AML" sheet. Make sure that both workbooks are open and run the macro.

Please Login or Register  to view this content.