Macro insert multiple sheet and insert age band based on numbers

Dear jaslake,
Thanks buddy. It works perfect.

Please provide comment.

hi jaslake,
Thank you very much.

Just have below queries. Please find attached sample file. In Sample file I have just added 2 columns and rename the heading of all columns. I am getting following error

1) Age band for last column with heading "United_India" is not created in new sheet. It shows some error.
2) Age Band in Newly crated sheet "SBI_General" in cell number F8 and F9 is coming in date format.

Please help me with rectification.

Got it space issue is resolved.

But Tried removing apostrophe in O3. It is changing to Feb-18 and the same is not coming in new sheet. How to control it?

and same is happening with cell no. D3, v3,

hi jaslake,
Thank you very much once again.

Actually I wanted to make this file as master file for all manual work.


Can you please help me with further code to process below task?

Code to convert value in Column J based on value in column I

1 If Value in column "I" is "Self" Keep value in column "J" as "Self". Irrespective of small letters or capital letters.
2 If Value in column "I" is "Employee" Keep value in column "J" as "Self". Irrespective of small letters or capital letters.
3 If Value in column "I" is "Proposer" Keep value in column "J" as "Self". Irrespective of small letters or capital letters.
4 If Value in column "I" is "Spouse" Keep value in column "J" as "Spouse". Irrespective of small letters or capital letters.
5 If Value in column "I" is "Spouce" Keep value in column "J" as "Spouse". Irrespective of small letters or capital letters.
6 If Value in column "I" is "Husband" Keep value in column "J" as "Spouse". Irrespective of small letters or capital letters.
7 If Value in column "I" is "Wife" Keep value in column "J" as "Spouse". Irrespective of small letters or capital letters.
8 If Value in column "I" is "Child" OR "Children" Keep value in column "J" as "Children". Irrespective of small letters or capital letters.
9 If Value in column "I" is "Son" Keep value in column "J" as "Children". Irrespective of small letters or capital letters.
10 If Value in column "I" is "Daughter" Keep value in column "J" as "Children". Irrespective of small letters or capital letters.
11 If Value in column "I" is "Sibling" Keep value in column "J" as "Siblings". Irrespective of small letters or capital letters.
12 If Value in column "I" is "Brother" Keep value in column "J" as "Siblings". Irrespective of small letters or capital letters.
13 If Value in column "I" is "Sister" Keep value in column "J" as "Siblings". Irrespective of small letters or capital letters.
14 If Value in column "I" is "Parent" OR "Parents" Keep value in column "J" as "Parents". Irrespective of small letters or capital letters.
15 If Value in column "I" is "Father" Keep value in column "J" as "Parents". Irrespective of small letters or capital letters.
16 If Value in column "I" is "Mother" Keep value in column "J" as "Parents". Irrespective of small letters or capital letters.
17 If Value in column "I" is "in-law" OR "in-laws"Keep value in column "J" as "In-Laws". Irrespective of small letters or capital letters.
18 If Value in column "I" is "Father-in-law" Keep value in column "J" as "In-Laws". Irrespective of small letters or capital letters.
19 If Value in column "I" is "Mother-in-law" Keep value in column "J" as "In-Laws". Irrespective of small letters or capital letters.
20 If any other value in Column "I" other than above 19 values, Keep value in Column "J" as "Please check relation". against all members of particular family
Also please provide provision to replace more relation if added in future


Code to convert value in Column K based on value in column J

In family description column need code to put the count of number of relation as mentioned in sheet "Example data"





Code to convert value in Column L based on value in column J

1 If Value in column "J" is "Self" Keep value in column "L" as "Self"
2 If Value in column "J" is "Spouse" OR "Children" OR "Siblings"OR "Parents" OR "In-Laws" Keep value in column "L" as "Depedents"
3 If Value in column "J" is "Please check relation" Keep value in column "L" as "Please check relation".
Also please provide provision to replace more relation if added in future



Code to convert value in Column M based on value in column L

1.Count Number of Dependents in Column L in family wise, then assign number to it. We have two Check point here.
one is Employee code [Column "B"]which will be unique for each family and second one is column "L" where "Self" andsebsequent dependents are their depedents.
Also please provide provision to replace more relation if added in future


After performing above task.

I need code to generate one more sheet for pertaining to all heading in SHeet "Age Band" like how we created New sheet with name of each heading.

This time new sheet to be created with Extension of text "demography". For Example first Sheet to be created as per your old code with name "Aditya_Birla" now with new code we should be able to create next sheet as "Aditya_Birla_demography" and create sheet "Apollo_Munich" and then create "Apollo_Munich_demography" and so on.

Now in Demography sheet I wanted create a template for each heading's as mentioned in sheet "Example_Aditya_Birla_demography". So in sheet "Apollo_Munich_demography" Template will be as per the age band mentioned below heading of "Apollo_Munich" in sheet "age Band".

Request you to help me on this. This will solve my huge time.

No Not yet.

can u help me on this?

Dear Jaslake,
the last code you provided is perfect. I just request you to a line of code to add additional equal number blank sheets with same name with string "_Demo" (e.g given in attachment)

Please help