Need help for creating new sheet with predetermined tabular column in it

Dear Experts,

At first, my sincere thanks to Holger and all other experts for extensive support provided. I need to develop a master to

maintain customer information, of which the excel spread sheet is attached.

It is already containing event macro, which does the following

1. Sheet1 - Column G is the master list of customers, which contains existing customer names.

2. For each of the existing customers, there is a separate sheet available, namely - Spencer, TATA, Ford, Kiwi.

3. The existing sheets - Spencer, TATA, Ford, Kiwi - cells A1 to R3 are password protected.

3. The customer name will be typed in sheet1 cell B5. If the name is in the existing list of customers in column G, then

excel should take me to the respective customer sheet (This function is not happening now, and for this also I need your

support)

4. If the customer name is not available in the column G, then it will be added in the column G, and a new sheet in the name

of new customer will be created at the end of the workbook.


Now my requirements are

1. The newly created sheet to automatically contain the tabular columns as in the existing sheets

2. Newly created sheet with tabular column should automatically protect the cells A1 to R3.

3. In the newly created sheet, Cell D1 to I1 to be merged, and it should show the name of the sheet (Name of the customer)

automatically.

4. If the name of the customer typed in the sheet1 is already existing in the column G, then excel should go to the specific

sheet having the name of the customer typed.


I know just copy and paste macro codes. I fully depend on experts like you for this. Can you please support to provide the

macro code for the above functions?


With sincere thanks for all

gm2612

Data Sheet1.xls

Hello Jaslake,

Wishing you a heppy new year. I am sorry for the late reply. The site was not opening in my computer for around a week. Now, I used your sheet, and it works just perfect.

By the way, if I want to open a new work book in the same folder (instead of a sheet in same file), can you please support with the code that I would have to add?

Thanks for the help.

Regards,
gm2612

Hello Jaslake,

Yes, each customer will have a separate workbook

"Template" will be in the main workbook, in which first sheet - "Sheet1" will have the cell B5 where the customer name will be typed. (Column G will have master list of existing customers, as indicated above in the first post.) Let us say, the sheets "Sheet1" & Template together live in workbook "Data Sheet1 v1.xls"

The new workbooks to be named with the customer names. (In the existing codes you have helped, if the customer is a new customer, a new worksheet in the name of the new customer is created. Similarly, instead of new sheet, a new work book in the name of the customer to be created)

Location of all these files will be in "\\ws1\myDocs\gmk\My Documents\Excel trials"

Can you please help me out?

Thanks and regards,
gm2612

Hello Jaslake,

Thank you so much for the valuable help you have done to me. The sheet you have provided is more than perfect. I thank you so much again.

boy_scout_salute.jpgRose-Flowers-Bouquet-1.jpg



With Warm Regards,
gm2612

Hello Jaslake,

Offlate, I thought of two more requirements in the same file. One is that enabling autofilter in the column B of the new workbooks created, and second is protecting both new and "Data Sheet 1 V2.xls" workbook. Can you please provide any help on the same?

Thank you so much in advance.
Regards,
gm2612

Dear Jaslake, Thank you so much for your help.

7020c_American-Spirit-Flower-Bouquet.jpg