Vlookup with VBA - dynamic ranges

[Will B]

Hi all,

I had an issue using a vlookup formula, and my other thread did not recieve any replies. I think its not possible. I basically wanted to define the file path for a vlookup using a concatenate based on the selection of a listbox populated with files from a specific directory. So I have turned to VBA to help resolve this.

So! I have X number of workbooks, with say 10 sheets, all with one table on each sheet. I have a master workbook, with the same 10 sheets and tables, which i want to populate, based on the workbook selection on the "Front Page". Imagine the X workbooks as various scenarios. These will be populated by the user, and then selected from another workbook to manipulate further. I will highlight what I am struggling with - first, the vlookup sub in VBA. I have used a number of online examples, but I really cannot get them to work! Secondly, is how can I get the chosen source workbook to become part of the vlookup range (Filepath) in a vlookup sub? Once I have done this, do I simply replicate it 10 times, once for each table I want to populate?

Any direction on this would be very gratefully received! Previous post is here

http://www.excelforum.com/excel-form...94#post2986994

[stojko89]

If you want to take data from different workbooks...you have to do the folowing:
- In the master workbook you need to set the file path\file name or a range or something where the macro can get the file path and name.
After that you need to use the.

Please Login or Register  to view this content.

The file path and file name can be used like Range("A1").Value
But you'll have to set it as a string like this:

Please Login or Register  to view this content.

The code then looks like this:

Please Login or Register  to view this content.

That is if the file name contains the extension also if not then you'll have to use.

Please Login or Register  to view this content.

After you open the workbook you can use the find copy ranges depending on what you're trying to do.

[Will B]

A great help to get me started - thank you very much!

[Will B]

Question - Once I have the specified workbook open, I would like to activate the window to proceed with the find and copy etc. Currently the code reads...

Please Login or Register  to view this content.

How would I go about having the workbook name as a variable, so as different workbooks are opened, the correct window is activated?

[Will B]

Question - Once I have the specified workbook open, I would like to activate the window to proceed with the find and copy etc. Currently the code reads...

Please Login or Register  to view this content.

How would I go about having the workbook name as a variable, so as different workbooks are opened, the correct window is activated?

Solved this by using the strName!

Now I am working out how to activate a workbook!

[Will B]

Solved this by using the strName!

Now I am working out how to activate a workbook!

Done! Many thanks stojko89

[stojko89]

You don't realy have to activate the workbook.
You just open it and then assign what is the search value and what range you want copyed and so on.
So it opens the book, copys and closes it again. and moves on to the next workbook.
Youre code will have to look something like this:

Please Login or Register  to view this content.