Program purpose: Enter a text input such as SPEC100E in main worksheet, the macro in excel will grab and read this SPEC100E worksheet in the workbook and copies data here to the main worsheet. I have about 45 different spec worksheets. ie why I would like to just type the spec sheet no in main sheet and then data will appear right here in main sheet.
VLOOKUP immediately comes to mind, but with 45 different sheets you'll likely need a VBA approach. (Unless you have a summary sheet, which would greatly simplify things, but without seeing what you're working with...)