Add or Update Worksheets based on Column Status from Master Data to Another Sheet

I Have One master data Sheet ("Application") in the excel table format. Table Name "APPLICATIONTBL".
In "X" Column there is status (Red, Amber, Green etc.)
I need to create a macro:
if the worksheets for any specific status available then it will update the data of that particular status . Like If the "Amber" Sheet are available the when we do the entry in master data ,it will automatically update all the data from master sheet for "Amber" Status" in Amber Sheet.
if the Sheet are not available then it will add worksheets and create the table with only that specific Status.
Like sheet name "Red" is not there, so it will create a sheet with "Red" name and pull the data of Red status from master data "Applications".
All sheets should be in Table format.
Any help will really help me a lot. Thanks in Advance.

Hi,
Thank you for you quick reply.
Very much surprised on your second approach ("automatically update the data one row at a time as each row of data is added to the APPLICATIONS sheet and the status is inserted into column X.")

I have updated/added the second sheet.

Thanks and Regards,
Sanjay Kumar-

Hi,
Thank You for your Support However having some problem in it.
1. Whenever i am starting to do the entry in Application sheet a pop window is coming.
2.After completing the entry in X Column the data populated in another sheet are coming out of excel table.
3. When we changed the status of X column in Application sheet it is not updating (like if we change the status of Screening pending to Joined then from Screening pending sheet it should be deleted and Added to Joined Sheet.)

What do you want now? Apart from that it is a bad idea to put data on different tabs.

Maybe so?

Please Login or Register  to view this content.

Hi Vraag,
Thanks for your time and support.
The Code is just doing the reverse/opposite which was my requirement.

The Application sheet is the Master Data.
1.When we do the entry in Application Sheet (it should not be deleted) from Application sheet and create another sheet based on X Column Status and update the data.
2. If the another sheet (X column status name) already exists then it will only update the data.
3.If we change the status of (X Column) of previous data (ex. screening pending to Joined) in Application sheet then It should be update/Add as per new status (Add entry in Joined sheet) and remove entry from (Screening pending) Sheet.
Example:
If We add one entry in Application Sheet and Final Status is - Screening Pending, Then One Worksheet with screening pending name will created (if already not exits ) and update the entry from Application sheet. If Worksheets name already exist then only update the entry.
Now if we change the old entry in Application sheet and for example Screening Pending changes to Joined status in X Column then,
that specific entry will be deleted from "Screening Pending" worksheet and added to "Joined" worksheet.

Hope I am able to clarify you.

HI,
The error are still coming when we select the whole Row Like (B4 to X4) or multiple or single row with X".
Once any selection with X Column or Row then Error showing same as previous .
Also data are going to another sheet (1st entry in the Table column correctly and after that 2nd entry out of table. Like if we do the "Joined" status 2 times then its 1st entry in Joined sheet is in the table , but 2nd entry is out of the table.

Except above the macro logic is correct.

Thanks and Regards,
Sanjay Kumar

Hi,
I have done 2 entries in the Application Sheet . And added one error tab in the sheet with error print screen.


Thanks and Regards,
Sanjay Kumar

Hi Mumps1,
Thank you for giving me attention , and giving your precious time to me.
You are really a very helpful person.
I am receiving still rum time error when selecting X column. and little bit issue in updating the columns.

Attaching the print screen in the error tab.
Again thank you for such kind help.

Hope you still help me


Thanks and Regards,
Sanjay Kumar

Hi,
If by mistake we Select the whole sheet of (Application Sheet) then below error occurs "run time error 6 overflow.

For updating the Sheet:
Any changes made in any row should update in its specific sheet.

Example : if any cell value changes from A4 to last row "AA" and its Final Status is "Rejected". then In rejected sheet the same changes should update.
Like if we change/update cell Q 4 date (01 feb 2020 to 01 mar-20) then in the rejected sheet it should also update/change to 01 mar 2020.
Any changes to any cell should update.

I am attaching the sheet with print screen for better understanding

thanks and Regards,
Sanjay Kumar

Hi,
This Code is not working. and After doing the entry in Application sheet , nothing is happening.

Hi Mumps1,
I have attached the error screen.

Hi,

I am try to express my requirement in more simple way.

The Application Sheet is the Master Data.
Requirement :
Filter the data based on Final status "X" Column and paste the data into its specific sheet in table form.
Like if the Filter data in Final status is "Screening Pending" then data will be paste in "Screening Pending" Sheet.


I have made one macro to update the "Screening Pending" Sheet , however its not Dynamic and works only for Screening Pending.
Also i have to run macro again and Again for this for every entry, So Automatic update in its Specific sheet are highly need.

Thanks and Regards,
Sanjay

Hi,
I think now the changes are doing fine.
But New entries in the Application sheet is not taking and giving the error.

Hi Mumps1,
Same problem.
Added correct sheet and entry moved to correct sheet
But the final status is not changing and its previous status is showing.
Done only one entry and attaching for your reference.
I have done one entry and final status is Joined
When i changed from Joined to Interview Select then it moves to Interview Select sheet
But the status is still showing as joined in Interview Select Sheet.

Hi Mumps1,
I think Its working fine!
I will test it more and hopefully it will work fine. If not I will be back for your help.

You are really genius , You made code so fast , I can't imagine.
I don't know i will be able to have knowledge like you. very Impressive and very helpful.

Thank You So much.
I want to be connected with You...

Hi Mumps1,
Can you help me once more !
If I want to having macro as you suggested 2 way.
1. Every time run the Macro.

can you make the macro from button click "Every time " when we want to extract the data to its respective sheet.
I actually want to analyze which will be easy and convenient , where chances of error is less.

Requirement :
It Filter the data based on Final status "X" Column and paste the data into its specific sheet in table form. (If sheet name was not then create and Paste the data)

Thanks and Regards,
Sanjay Kumar

Hi Mumps1,
If i am working as new startup in this then it is the best way.
But when i want to migrate the previous master data in it , i have to cahange it manually each row.
Like i have 2000 rows in different sheet ,which i was using earlier.
Now i want to migrate all the data in it, it will not work.