Home

A Macro engaging a drop down box

Roger Gaspar
New Contributor

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':

 

Front Page extract.jpg

 

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.

 

Is it possible?