Drop down menu that links to a sheet in another workbook

Hey everyone,

I am trying to figure out how to do the following

1. I have a dropdown menu with the names of the sheets in another workbook
2. When the user selects a option from that dropdown and clicks a "run macro" button the macro will go to the other workbook > go to the sheet with the same name as in the dropdown and copy A1 and paste it back in the original workbook.


It sounds relatively simple but I could not figure it out at all.

Thanks in advance for any help I can get, i absolutly love this forum!

paste this code into a module. It will open the file chosen. Your button will run: Open1File

Open1File will launch the function with the chosen cell value that has the file.
in the example below it is cell E2
OpenNativeApp Range("E2").Value

cell E2 is filled when user picks an item from the combo box. (the index of the choice is put in E1)
but Vlookup fills in the actual value of the file into E2 using Vlookup:
E2.formula = VLOOKUP(colShown, RangeOfData, colOfFiles,0)

Please Login or Register  to view this content.

Hey RanMan,

Thank for your help! It works but it does not exactly what I want it to do. Maybe I can make it a bit different so that there are less variables.

Lets remove the button as I think that will only give me headache (not you im just not that good with excel)

Is it possible to have a name in cell A2 and with that name go to a pre detiremed workbook and go to a worksheet in that workbook that has the same name as the cell in A2 that we just selected?

So for example A2 in Workbook Y = Pirate
So it will go to workbook X and go to the sheet thats called Pirate

I hope this makes it easier for me to understand. Sorry if i caused any confusion and many thanks again!