Create List based on criteria (VBA)

**pipoliveira** · 04-29-2015, 09:53 AM

Dear all,

I hope you are all well.

I am struggling to create a VBA code under Worksheet SelectionChange event.

I have attached a test file.

In Sheet1 I have a table with some information.

Now in Sheet2 I need to create dynamic table based on selection (Yellow cells).

For example, in Sheet2, if a user selects a region (e.g. EMEA) I want to populate all employees ID; Name; *** and Manager name.

Tried to use functions but could not do it.

Any assistance will be highly appreciated.

Best regards,
Filipe

**skywriter** · 04-29-2015, 11:46 AM

Hi Filipe,

You said you are struggling to create a VBA code, so I will assume that you have some experience and are interested in expanding your knowledge of VBA code. What I have done is write some code to help you achieve part of your objective as I see it. I want you to try and finish it. I am here to help you.

What I did was on sheet2 I made a list of the employee ID's in column Q to serve as the data for a drop down list which is where you will pick the ID numbers in A3.

The selection change event is triggered when you click on a cell other than the one that is currently selected so I instead chose to use the change event. The code is triggered every time you make a different selection in the drop down list.

The code in the change event checks to see what cell triggered the change event and if it was cell A3 it calls a macro, so look in module one and you will see the macro that is triggered. The macro clears any previous data from sheet2, auto filters sheet1 according to the employee ID selected, hides the columns you don't want on sheet2, copies the data over to sheet2, unhides the columns that were hidden and removes the filter.

The code is below and the workbook is below the code.

Please Login or Register  to view this content.

Please Login or Register  to view this content.

**pipoliveira** · 04-30-2015, 10:35 AM

Hi skywriter,

First, let me thank you for your time and focus on this.

I had a formula that worked well for this purpose. I am more confused on the other two tables.

The employee ID will only show one row of data which is the employee that you choose. The other two (Manager & Region) are dynamic tables. For these ones I am struggling to create the VBA code to show/create a table based on the criteria. If the criteria changes, the table will change it's contents as well.

Yes, I know a bit of VBA experience but to be honest, I know pieces and bits that I join together. Unfortunately I do not have much spare time to learn it as it should.

Any ideas will be highly appreciated.

Best regards,
Filipe

**skywriter** · 04-30-2015, 11:07 AM

Good Luck.

If your original request is fulfilled please click the Thread Tools drop down box above your first post and choose solved.

If you are happy with my help, please consider clicking the add reputation button in the lower left hand corner of this post.

**pipoliveira** · 04-30-2015, 11:21 AM

Hi skywriter,

Thanks a million for your support.

This is just what I was looking for.

Now I have to struggle (not that much now, thanks to you

) on fitting on my own file that has +50 columns and +500 rows...

Once again, your assistance on this is highly appreciated. Thanks for all your help.

Cheers,
Filipe

**skywriter** · 04-30-2015, 11:34 AM

I can explain the code where needed to help you adapt it to your actual file.
Thanks for the rep. points.

**pipoliveira** · 04-30-2015, 03:15 PM

Hey Skywriter,

I will accept your offer indeed and thanks a million for that.

The only thing that I don't understand on your code is how it fills the information on the right columns?

I mean, there are no references about the columns where to update the required information or else, the code itself is recognizing the same column names??

As I mention, my real workbook table have +50 columns and +500 rows and I might want to retrieve the information e.g. from column 5; 25; 3; 16... if you know what I mean.

Thanks a million in advance.

Cheers,
Filipe

**skywriter** · 04-30-2015, 03:36 PM

On sheet2 in cell F3 you wanted to filter by manager name. Changing the manager name triggers the worksheet change event. The value of the manger name is passed to the Sub named FilteredManagerData(Manager). This (Manager) is the name of the manager getting passed from the worksheet change event to the sub procedure.

The section of code below filters your data on sheet1 according to the manager name, it then hides the Gender column and the Manager column because you didn't want that data on the second sheet, it then copies the visible data and simply pastes it to cell F5 on the second sheet. Then it unhides the hidden columns and removes the filter. This of course all takes place in a fraction of a second and you never notice it because you are on the second sheet when this happens and screen updating is turned off.

Please Login or Register  to view this content.

**pipoliveira** · 04-30-2015, 03:44 PM

Thanks Skywriter,

Yes now I see how it works

And yes, I was wondering why it was filtering... I should have used a bit more my burned brain hehehe.

Now the question will be, with my real table, will I have problems with performance? With all the filtering and copying the info?

Well, I will only know as soon I'll try it.

Anyway Skywriter, your help was outstanding on this matter. Will not bother you again on this subject. Unfortunately I cannot add more reputation...

Thanks for all your time and help on this.

Cheers,
Filipe

**skywriter** · 04-30-2015, 03:52 PM

You are not bothering me. Everyone here, is here to help, if I don't reply to a post I'm sure someone will.
Good Luck!!!

Create List based on criteria (VBA)

LinkBack

Thread Tools

Rate This Thread

Display

Create List based on criteria (VBA)

Re: Create List based on criteria (VBA)

Re: Create List based on criteria (VBA)

Re: Create List based on criteria (VBA)

Re: Create List based on criteria (VBA)

Re: Create List based on criteria (VBA)

Re: Create List based on criteria (VBA)

Re: Create List based on criteria (VBA)

Re: Create List based on criteria (VBA)

Re: Create List based on criteria (VBA)

Thread Information

Users Browsing this Thread

Similar Threads

[SOLVED] Create list of combinations based on changing criteria

[SOLVED] Create a distinct list based on other criteria

[SOLVED] Create list based on criteria and is unique

[SOLVED] Create a list that has more than one column based on a criteria

[SOLVED] Create a separate list based on criteria in another list.

Bookmarks

Bookmarks

Posting Permissions