Pulling Range of Cell Values Based on another Cell's Value

Thank you Millz! That was a simpler fix than I was expecting.

I am trying to understand why .Value is needed after the larger range you showed me, but not needed for the single cell as I had it.

While this may be my best option, I am trying to figure out how to expand this for many potential cell value options. In my above example, I had "A" and "B" but I may need to do this for 50 plus letters and numbers. As well as ten different sections where the letters and numbers can be manually selected. Each letter/number would have two columns where their data is stored and the data for each ranges from row 2 to row 36. When the data is copied from the columns on sheet2 it is pasted all into columns B&C and each set of data is copied below the last set of data. I have included an example of what I am talking about, but I only included letters "A" through "I" versus the 50 plus options I would like to include. I also only have three selection options instead of the 10 I would like to include.

Is it possible to simplify the code instead of having the number of lines of code I will need to do this the current way?

Please Login or Register  to view this content.

Millz, you are awesome! Thank you!

I need to play with it more to understand how it works, but it seems to work great so far.

Hello Millz, I set it up to use the whole alphabet and that worked great! I also tried using integers and symbols as the value in cells A30, B30, C30 etc. It appears this code does not like integers and symbols. Is there a way to change the code to take either integers or symbols as an input in those cells? Instead of using integers, I tried using the names for the integers: zero, one, two etc. and instead of using the full name, it used only the first letter for each, "Z" for zero.

I also noticed that it does not differentiate between upper and lower case letters, is there a way to adjust the code for it to distinguish the difference?

Thank you again for your help!

Millz, I have attached a two worksheets with detail around what I am doing.
I am using the drop downs in A1, C1, E1, G1, I1, K1, M1 and O1 and selecting values in each. The code you provided is slightly modified to pull data from sheet 2 for each value selected.
Upper case and lower case letters and symbols have different data sets associated with them. So far I have the data for the upper case letters on Sheet 2 and will eventually have the other data. I am hoping to be able to select a value(s) from a drop down and click the generate star and have it pull the data from Sheet 2 to the cells below the selected values on Sheet 1. The vba code is modified to work for data sets of varying lengths (number of cells in a column, but always two columns) but so far all the data sets are the same size.

If it helps the code to interpret the selected value, I could use names for the numbers and symbols instead of the numbers or the symbols themselves. For example: Zero instead of 0. The code would then need to be able to tell the difference between a capitol Z and Zero, since replacing 0 with Zero just causes the data for Z to be pulled from sheet 2.

If the code were to recognize the difference between Z and Zero, then I might be able to differentiate between upper and lower case letters by adding "Upper Case" or "Lower Case" to the name of each value. For example: "G Upper Case" and "g Lower Case"

The file "Messin With Macros4" has the natural numbers and symbols and gives an error when one of those is selected and the generate button is pushed.

The file "Messin With Macros5" has the names for the numbers and symbols and will return the values for the first letter in the name of the value selected.

Thank you again for your help with this as I struggle through trying to understand bits of code.