Advanced Filtering Data Pulled Form Multiple Worksheets And Displayed On One Sheet

Hello,

First off I dont know much of anything when it comes to programming in Excel with VBA, or programming in general. Ive spent a couple days in my free time trying to gather, search and tinker around with making things work with limited knowledge the way I need it to work. So excuse my poor coding for all you Expert Coders.

The attached file was originally from someone else, but I need more out of it. I added Base_3 and 4 tabs and messed around with the coding some to get close to what I want. I also added the Base column in the tables and put the value number in the column matching the tab name value.

So I will try to explain what I need.
When I am working in Excel, similar to what is seen in the example file, I start out with one tab/worksheet named "Base_1". As I am working, I will create new Tabs and rename them "Base_2 and so on". while I am working and at the end of my work, I would like to use a Data Filtering Table as exampled in the file sheet name "Result". I would like the Filtering table to filter out a name or base value when displayed in the filter options. I could have up to 20 Tabs/worksheets by the end of my work or more or less.

- If I want to filter out a specific Base value say 2, then I want A) the table to display all the data form worksheet Base_2 with no name filtered and B) with a name filtered.
- If I dont filter out a specific Base or Name, then I want the filter table to display all data from all worksheets
- sometimes not all my tab names will be names exactly "Base_1 or Base_2" and so on, sometimes I may add more text such as "Base_3 NW" or "Base_3 SWNW". So if it is possible to have the macro to search for tabs containing Base_# with added wildcard text, then that would be great, if not then I can live with naming a Sheet/tab name exactly.

What I have now:

Please Login or Register  to view this content.

Let me know if you need more explanation from me.

Again thanks to those viewing this and helping out, much appreciation.

-Brent

Hi disepyon

Perhaps this is what you are after...

Please Login or Register  to view this content.

Ok well I am opening this thread back up as not solved yet as I want to take this Macro a few steps further.

This is what I also want out of this filtering macro:
- If I type in jo for salesperson, I want the macro/filter table to return all salespersons containing jo, whether it be at the start of the text string or somewhere with in. So John and Joe should show up

- Also id like for the macro to only pull values in the value column between lets say for this example from greater than 0 and less than 200. So for the template I use for work, there will be quite a lot of people who have no integer in the value column and it would be just 0 or a blank cell. when filtering for name or Base, I only want salespersons to show up that have value and all who dont have value wont display in the filtering table. But for this example it will just be everyone from 0 to 200 value.

- Finally id like for the macro to sort in order the values from greatest to smallest in descending order (largest vale shown on top and lowest value shown on bottom).

Please use the attached file in this post.

I am really stumped right now, tried recording my self and looking at the recorded codes, but not sure how I would incorporate it in the current macro.

Thanks for viewing and the help, meanwhile ill be at it...
-Brent

Hi Brent

K, Have adapted code to suite all requirements except the "*Jo*" Filter...Below the code is a snippet of code...This is the jist of what the code will look like...No time right now to incorporate....Will have a look on Monday again...

Please Login or Register  to view this content.

For the "Jo" Part...This works but must be integrated into the different scenarios....Will have to add "Jo" to your drop down though or remove the error filter

Please Login or Register  to view this content.

AWESOME!! got this figured out now. Sorts from largest value to smallest and I got it to group the Base numbers while retaining the value sorting.
The Partial name search and pulling and displaying everything containing what is typed in, for this example salesperson, works like a CHARM!!
The Message box I do not care about much anymore as displaying a blank table is fine with me, just means that nothing was found. I will play with it maybe.

here is the last edited Code:

Please Login or Register  to view this content.

Thanks again SinTek for showing me the right path on a lot of this. will Rep you again.

CASE CLOSED, SOLVED!!

To flag the thread as solved, please select Thread Tools from the menu link above and mark this thread as SOLVED. Thanks.

Glad you got it sorted...tx for rep +