Create and populate sheets based on values in master list

chorton2 Posts 1 Registration date Tuesday April 26, 2022 Status Member Last seen April 26, 2022 - Apr 26, 2022 at 03:49 PM
vcoolio Posts 1362 Registration date Thursday July 24, 2014 Status Moderator Last seen September 29, 2022 - May 6, 2022 at 03:59 AM
I have seen a few similar forms but cannot seem to achieve the functionality I'm hoping for.

My workbook will have a master spreadsheet that is periodically downloaded from an online data base. The master sheet has contents in Columns A-BB and is approximately 1000 rows long depending on the changes to the data base. Column AC has a value for who is next to go on the ticket. There are 10 entities that could be next to go for example "CTLOR", "COMOR", "FTMETR", etc... And column AO has the dates that they became next to go on the ticket.

I would like to have VBA code that creates a new sheet in the work book for each unique value in column AC (10 sheets) and names the sheet based on who is next to go. Then, IF the party has been next to go for more than 30 days, copy that row into the corresponding sheet that was created.

So after running, there will be 11 sheets total including the master and each sheet will contain rows of data from the master that belong to the corresponding member and have been outstanding for over 30 days.

Im new to VBA but hoping to get pointed in right direction here. I was able to achieve something close using formulas, but i would like to have more customization with VBA. Ideally, I will be able to break down the tickets even further by ticket type within each sheet which is stored in Column E.

Thanks

3 replies

vcoolio Posts 1362 Registration date Thursday July 24, 2014 Status Moderator Last seen September 29, 2022 252
Apr 27, 2022 at 02:12 AM
Hello Chorton,

Just a suggestion.
Would it be a better proposition to create new worksheets for each unique value in Column AC(I'm assuming that there would be more than one row of data per entity in the master sheet), with all their data, and then break it down as required simply by using filters on each worksheet?

What do you think?

Cheerio,
vcoolio.
0
rizvisa1 Posts 4479 Registration date Thursday January 28, 2010 Status Contributor Last seen May 5, 2022 767
May 5, 2022 at 07:58 PM
see if this help
https://ccm.net/forum/affich-258700-interesting-question-for-excel-champions

Basic idea is same as @vcoolio said. You copy the the column AC and get unique value of it. then for value you can apply filter on master sheet and copy the data
0
vcoolio Posts 1362 Registration date Thursday July 24, 2014 Status Moderator Last seen September 29, 2022 252
May 6, 2022 at 03:59 AM
Hey Rizvisa,
Good to see you back! Where have you been?

Cheerio,
vcoolio.
0