I have a quite large Workbook comprising a basic data array of about 110,000 distinct unique values and a 'Front End' which uses a series of drop down boxes and a 'host' of VLOOKUP formulae ultimately to provide a sub-array of values: the particular combination of all the Drop Down Boxes will provide between 4 to 10 values.
The next stage I need to do, is to harvest the sub-array of values to create a new array - essentially the new array will add new information about the 110,000 distinct unique values.
I can copy and paste the sub-array values, one by one but I would need 'manually' to repeat that process over 8000 times! So I have been trying to see if I could set up a Macro. I am hopeless at Macros anyway but I cannot see any way of engaging a Drop Down Box. Here is the final part of the 'Front End':
The Drop Down box has 49 options and each option will provide separate values to the immediate right. Those values are to be copied and pasted away into another Worksheet. I need the macro to automatically run through each option of the Drop Down Box in turn, pausing to copy the sub-array values, paste the values into a separate worksheet consecutively and then run the next Drop Down box option until it has exhausted all 49 options.