Workbook using For loops to compare 2 lists is slow

Hi, this is my first post on the forum, so I'll give a quick bit of background about my Excel/VBA knowledge. I have a basic grasp of the syntax of programming languages (i have some experience with visual basic, html and action script, but I wouldn't say I'm proficient) and I'm aware of quite a few of Excel/VBA's functions.

I work in a communications team for a large retailer, and we're going through a system changeover in our stores in a couple of months. The system being changed is one which enables us to message stores with important information. While the new system rolls out to stores we will need to send communications to stores using both the old and new systems. If there are any legal issues we need to communicate, we need to ensure that we send the message to the right stores i.e. only send it on the old system to stores still using the old system, only send it on the new system to stores using the new system. These messages may be targeted to certain stores, which poses a problem of sifting through a list of up to 2500 stores and finding which ones are using which system.

To solve this problem I am developing a workbook where on Sheet 1 the user can input a store list (a simple list of numbers, 1 number per cell), on Sheet 2 there will be a regularly updated list of stores using the new system (again, a simple list of numbers, 1 number per cell). On Sheet 1 is a process button, which when clicked launches a function that finds the stores on Sheet 1 that are also on Sheet 2 and copies them on to Sheet 3, and copies the stores from Sheet 1 that aren't on Sheet 2 on to Sheet 4, creating 2 store lists to be used on the 2 systems.

I have attached a workbook that contains a working version of this, but it is hideously slow (nested for loops) when I start to ramp up the lists to over 1000 entries.

I have read in a couple of forums that VLOOKUP can be used to do this sort of thing much faster than using For loops, but I can't seem to get the right result using VLOOKUP. If anyone can provide a bit of help and point me in the right direction to speed this workbook up at all i'd be very appreciative!

StoreListGeneratorTest.xlsm

Hi Adam,
It appears you want to compare both sheets. if columns A of both sheet match, you want to copy the matched row in sheet3.Is this right?

Amazing, thanks Olly! That's exactly what I need it to do!

Just to quickly answer your first question: the store lists I'll need to input onto Sheet1 come from other areas of the business that won't have access to which stores have switched over to our new system, and the list of stores on Sheet2 will be growing day by day as we roll out the system - so I think this is the best solution.

And now it takes a second rather than five minutes! Thanks again for your help!