Excel VBA to select next item on the drop-down list and loop until the end is reached

Files
1. DataSource File [ This file has Various Code/ID data]
2. ImportData File

What I have:

1. I have a simple web query to get specific data from website, with data refresh enabled.
2. Thus basic structure of WebQuery is

• a) Look for value in Cell B1, B2 & B3 and refresh and display data in Cell/Range A7 onwards
• b) B1 & B2 is Drop-Down Validation List from DataSource File.
• c) B3 has Vlookup formula based on value of cell B1
• d) C1 has Vlookup formula based on value of cell B1

B1 has 90+ values, B2 has 7+ values to choose from the Drop-Down-List.


What I would like to have is then:-

1. Loop through the entire list of value in Cell B2 [Thus data is imported for 7+ B2 values with B1 & B3 remains same]
2. The result of web query runs and gets the data.
3. Save the WebQuery-result[Step2] of each value of Cell B2 in Separate Workbook in separate sheet for 7+ values
4. Name the newly created Workbook the name in the C1 & the individual sheet will be based on value in Cell B2
5. Then go to the next value in Cell B1, and start the entire process all over again until the end of B1 is reached.

I know the computer might slow down if this is done in one go, however i can edit the source file[DataSource File], to process 10-15 values of B1


Image for Ref:

ExcelForum_Vba Query.png


Thanks in advance.

Here is the Web Query:

Can i use this in the VBA for main question/problem

Please Login or Register  to view this content.

What i did is taken the data from DataSource to new sheet called source and naming respective tables as "mainID", subIDType", "coreID" etc.

I managed to come up with this code. This is just a initial loop which will select values from named range

Please Login or Register  to view this content.

Now I want the web-query to run when values are set as per above.