VBA To Create Multiple Queries When Data Varies

Hi All,

I have a database and I need to be able to separate data the same way each time, but the data is always different. I have attached a sample spreadsheet as an example. Basically, I'll have the "Main" tab and then will need to separate the data by Team, By Year and then by State. The way I am accomplishing that now is by manually filtering every single time (terrible!). The Sample file shows what the main data format looks like and then a sample breakdown of TeamA (only), I'll create separate files for TeamB-TeamD and break the data out the same way.

So ideally, I would like to be able to upload this "Main" data dump to MS Access and with a combo box set to "Team A" and then create all the individual tabs or queries that I make manually by filtering. The thing is that every time I have a new data dump there will always be different numbers of Teams, States and Years. So I think the VBA would have to know to only create tabs/queries where years, teams and states are present and exclude where there are gaps.


Thanks!!!

That is a horrible way to store data. Ive had users do this too.
So what I did was make an XL macro to parse the data sheets. It takes the row blocks and copies it to an 'export' sheet, in the correct db layout.
This export sheet is linked to the access db as a table. So once the parsing is done, I open the Access db, and run the import macro.
The macro pulls the xl data into a table where NOW it can be reported.

I would have no issue with manipulating the data first in XL via a macro. Would you be able to post a recommended macro per the sample data. Keeping mind that the sample data is a fraction of the size the actual file will be. The actual file could have 10 years worth of data, up to 20 different teams and every state and that could be 20,30 or 50+ tabs in XL.

Can someone possibly help and post a recommended macro??

Thanks!