Why would you need a macro? What's wrong with VLOOKUP? Just set up a query sheet that populates when required

Adding individual sheets is a bad idea, you will just end up with so many sheets that you won;t be able to track them