Auto-Populate Cells Based on Drop Down Selection - VBA Excel

Hello,
Please see the attached two documents, one is a template and the other is a master. They are both simply an example with generic data and headers. I am trying to make a document ("Template") which contains a drop-down in a column, Header 6, in this example. When a product is selected from that drop-down, I would like all of the information in the Template document to be auto-populated into the correct columns with the data found in the Master document.

The Master document has various tabs for each process as you can see. In the real document, I made this example for privacy purposes, the headers are not named numerically as so. Also, the document is very fluid, so columns will be added and deleted a lot. Therefore, the auto-population has to be able to find the correctly named header and insert the data based on the selected product from the drop-down.

Please see the attached Master and Template examples for more clarification.

What do you guys think is the best way to tackle this?

Currently, I have started by using a change event sub. I have started using a find function to locate the column in both the Template and Master document based on the header names, that way if the columns are moved around it will still find the correct column. There are over 100+ columns in the real document, so there are a lot of arguments in the function. Once I have the correct locations, I was thinking of putting all of the master information and locations into an array, and all of the template locations into another array, then using both arrays to place the information in the template.

Any better ways? The way I'm doing it seems very long and tedious.

Originally Posted by dflak

So what you want is to pick a process page, and then copy the information to the template page. Is this correct?

I want to select any Product from the dropdown in column "Header 6" on the Template document, which will then copy the corresponding Product data from each Process tab in the Master document to the correct cell in Template document.

Originally Posted by dflak

So what you want is to pick a process page, and then copy the information to the template page. Is this correct?

I want to select any Product from the dropdown in column "Header 6" on the Template document, which will then copy the corresponding Product data from each Process tab in the Master document to the correct cell in Template document.

Originally Posted by dflak

I changed the name of the files for testing purposes. You can change them back. I indicated in the code where to do this. Only the template has code. The Master workbook does not.

I put tables in the master workbook, but it turns out I could not use them, so you can use the original master workbook as is.

I did make a table out of the template because templates copy down formulas automatically.

Go to Column F and click in the cell where you want to enter data. This should be on the row immediately below the existing table. The code will create a new row for the data that says, "Select Product." You can then use the dropdown box to select the product and the code will look it up.

Very clean way of doing it. I am going to try and add this to my real document, and make the necessary changes to make it work. Thank you very much! I will let you know if I can get it to work on my real document.

Originally Posted by dflak

I changed the name of the files for testing purposes. You can change them back. I indicated in the code where to do this. Only the template has code. The Master workbook does not.

I put tables in the master workbook, but it turns out I could not use them, so you can use the original master workbook as is.

I did make a table out of the template because templates copy down formulas automatically.

Go to Column F and click in the cell where you want to enter data. This should be on the row immediately below the existing table. The code will create a new row for the data that says, "Select Product." You can then use the dropdown box to select the product and the code will look it up.

So I'm running into an issue. When I try to open the master file, it is resetting the change event macro and going back to the start of the script. I have disabled the events right before opening the workbook, but it still does the same thing. It makes it through the top part of the code, so the file does exist. Then when it goes to open the workbook, the macro starts over again as if enableevents is not disabled. I have checked the Locals window and see that enableevents is indeed false when running, so I'm not sure what else I can do...

Please Login or Register  to view this content.