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
1344
Registration date
Thursday July 24, 2014
Status
Moderator
Last seen
May 17, 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
1344
Registration date
Thursday July 24, 2014
Status
Moderator
Last seen
May 17, 2022
249
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
769
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
1344
Registration date
Thursday July 24, 2014
Status
Moderator
Last seen
May 17, 2022
249
May 6, 2022 at 03:59 AM
Hey Rizvisa,
Good to see you back! Where have you been?

Cheerio,
vcoolio.
0