Seperate Status & Verticals in different sheets & workbook

[Gaurang Mhatre]

Hi Friends,

I require one solution for my Project Tracker which i am maintaining in my office currently. i am unable to find code for this requirement in search box of this site.

Basically, i need a macro which will split status in different sheet. There are 7 status in masterdatabase sheet then it should create 7 status sheets in same excel as per the status(Column H) and vertical (column D)

We will be sending mailers to 2 verticals stakeholders one CCBG+Remittance and other Consumer vertical stakeholders. Thus i have one Main Masterfile which contains Masterdatabase i want to split the excel into different subsheets as per given below crietria.

Thus, for vertical CCBG+Remittance (we will be clubbing remittance vertical with CCBG) one excel should contain 1) Masterdatabase sheet which contains only Remittance and CCBG data without changing the formulas.

2) Other sub sheets should contain Status bifurcation which will be extracted from Masterdatabase sheet of CCBG+Remittance. For E.g. if there are 4 status in Masterdabase sheet of CCBG+Remittance then it should give 4 subsheets without changing the formulas which are there in masterdatabase sheet.


Another excel, which will contain Masterdatabase sheet of Consumer Vertical only and status will be bifurcated into different subsheets of excel which will be extracted from Masterdatabase sheet of Consumer without changing the formulas which are there in masterdatabase sheet.


Status are as follows in Column H of Masterdatabase sheet
Under Discussion
BRD Submitted
In Development
In UAT
In Production
Dropped
Hold
Live

Vertical are as follows in Column D

CCBG
Consumer
Remittance

For Eg. If status in masterdatabase is IN Development. Then it should create In development sheet and whatever projects are there in In-Development sheet should get captured in that subsheet no other status should be captured without changing the formulas which are there in masterdatabase sheet.So if I add more data in masterdatabase going forward, macro should run accordingly and should refresh other sub sheets.


I have attached excel for your reference where I have manually copy pasted the status in subsheets from Master database. But if you see the subsheets there are minimum column kept as per the stages (status) if you compare to Masterdatabase sheet. So will the macro run accordingly and will keep extra columns as blank or apply formulas.

Attached:
1 excel which is main excel...masterfile
2 excel (CCBG+Remittance split and Consumer vertical split excel) which i want to be run through macro, currently i am doing it manually.

Hope there is solution for it which will help save time.

Thanks alot for the help in advance.

Regards,
Gaurang Mhatre

 

[Chihiro]

This is simple if you have access to PowerQuery.

If not, MS Query will also do the job (search ADODB.Connection). Though it will require coding.

However, your column header set up isn't ideal. For ease of coding and efficiency, it is recommended column headers contain no space and kept short (much like relational databases best practice).

Are you opposed to this?

If above is undesirable, another way to do it is through Advanced Filter, though this would need Main File to be opened and will need hidden sheet with criteria range set up.