help with vba/macro for vlookup from multiple files to one file

Hi,

i'm trying to make a report that will show shortages for materials, and i want to do this as easy as possible

i have attached the shortage report, and bom1 and bom2 files for better understanding

what i want to do is, make a macro/vba that will make vlookup in "shortage report" file for each new bom file i copy in location D:\shortage

right now i have 2 bom files, but there will be many more to come ...

i really don't know where to start. any support is welcome

if you have any questions, please let me know

In the Shortage report, will the bom list in row 4 befilled in when the bom is put in the folder or will the fields be filled out before a bom exists..

i.e. when a bom is added to the folder, the macro would check if it exists in the report, if not it would enter all the details?

OR

Headings bom1 ----bom1000 are already filled in and the qty is added when the file is placed in the folder.

[QUOTE=JRidge;3885236]In the Shortage report, will the bom list in row 4 befilled in when the bom is put in the folder or will the fields be filled out before a bom exists..

i.e. when a bom is added to the folder, the macro would check if it exists in the report, if not it would enter all the details?

QUOTE]

correct

row 4 should be filled with the bom file names when i add another bom file in location D:\shortage location( i.e., if i add a "bom3.xls" file in the location, the macro should populate cell "N4" in shortage report with the file name, in this case "bom3.xls", "bom4.xls" in cell "O4", "bom5.xls" in cell "P4" and so on )

best regards

Ok. am playing with it..

will the bom always be in the same format? like the examples.

yes, it will always have the data that needs to be pulled from the same column

Well this should be fun

Try this out.

Put all files in your D:\shortage\ folder

Hi sacerdot,

Probably the code could be a lot neater and compact, I am just learning Vba and no expert so i don't no all the shortcuts etc.

Maybe some expert on here will have a look at what i have done and say there is an easier way to do it

Anyway, bom100.xlsx has all individual components and no repeated.

I take it you mean component "a" could be repeated a number of times in Column A......?

hi JRidge,

first of all, thank you for your support, you are doing really great

i have edited my previous reply and reattached the file, as i uploaded the wrong file ... but yes, you understood my point right

Ok, I will have a look and get Back to you

thank you :D, anyway, this is not anything urgent, so take your time, enjoy the weekend, etc.

In the bom is there any other data in the other columns?

yes, there will be, but it can be deleted(or i can delete it ), i don't need anything else except component (column "A") and quantity (column "H" )

Thats Good, Will try something and get back to you

Try This:

Fully Test and let me know

i just tested it and works perfect, just what i wanted.

many many thanks!!!!

now, can the macro also clean the boms? everything that is not on column "A" and "H" to be deleted. if this is too much, i can just do it manually

Yep, I was thinking that aswell....

But as the Macro doesn''t actually save a new copy of the bom it doesnt need to clean it.

If i am right the Macro to sort and then Combine Duplicates in the bom will just overwrite columns B and I with the new List and Figures, Once it has the Copied it to the Shortage Report, It closes the bom without saving the changes...

Try it... Make a new bom say bom14, fill in column B and I with anything in the rows that have components in A..

Yeah no need to Clean the bom...

The Macros and Code do everything that is required.

just tested and you are right.

i also tried to add another file that doesn't contain the "bom" word. can the macro work on any file that i put in the folder except the shortage report file?

Originally Posted by sacerdot

just tested and you are right.

i also tried to add another file that doesn't contain the "bom" word. can the macro work on any file that i put in the folder except the shortage report file?

Examples of File Names would be too much...

Would all files have the same setup inside..ie. Like the bom contents..

the files names could be anywhere from 12 digit number to a combination of 20-40 letters and numbers...so probably any kind of combination possible

the setup inside is the same for all files, ( component on column A, quantity on column H, and all sort of details that i'm not interested on the rest of the column/rows )

i tried changing the directory for the bom files ( with different name combinations) to be separate from the shortage report, and vlookup works ok if the boms are clean ( details only on column A and H )...if there is info also on other column/rows, vlookup gathers different data

Originally Posted by sacerdot

i tried changing the directory for the bom files ( with different name combinations) to be separate from the shortage report, and vlookup works ok if the boms are clean ( details only on column A and H )...if there is info also on other column/rows, vlookup gathers different data

actually the vlookup works ok if any other bom files are closed, if i have one of the bom file also open, and i click on the import bom button from shortage report file, the vlookup gathers different data and overwrites the other boms also...does this make sense?

nevermind )...i was just not using the macro right

Thanks again JRidge. This is exactly what i needed!!!

no need for any changes.

1. With the opening any file, it would be best to move the boms etc to a different folder.

Make a new Folder in the Shortage Folder Called "bom"

2. In the shortage folder only have the Shortage report.

3. In the bom folder only put in files which meet the criteria of the bom files (Doesn't matter what they are called).

4. Use this code:

just tested and worked excelent! just what i wanted.

many thanks JRidge!!!

Perfect.

If the Solution is what you wanted the please mark this thread as solved. (Top of Thread, under "Thread Tools").

Also if you think i helped Please don't forget to add reputation using the * in one of my posts..

Regards

JRidge

done that. thanks again