Macro for creating a new excel sheet based on certain conditions Closed

Question

Dear Experts,

Greetings. My sincere thanks for the support you providing thorugh this forum. Now stuck with some requirements in the microsoft excel. I request you to support me. This is my first interaction in this forum.

I will have a master list of customers in the sheet1, column "G". I will enter the name of the customer in the cell "B5". 

I will have a specific sheet for each existing customer (existing in the master list) in subsequent sheets, say sheetCustA, sheetCustB, SheetCustC...

1. If the entered customer name is already existing in the master list in column "G", then after entering the customer name in the cell B5, the sheet will have to go the respecting sheet with the customer name. 

For example, I have the Customers A, B, C, D, E and R in the master list of customers. If I enter "C" in cell "B5", then it should automatically take me to the sheet - "SheetCustC".  

If the customer name I am typping is not existing in the master list of customers, then 

1. it should create a new sheet in the name of the new customer,
2. It should copy the format from the previous sheet (Format is a common template for all customers)

For example, in cell "B5", if I type "G", then it should compare with the master list of customers, and since it is not appearing in the current customer list, it should update the master list with "G", and should create a new sheet with called - "SheetCustG", and then copy the format from previous sheet and paste it in the sheet - "SheetCustG".

Can anyone please provide me a macro for this application?

My sincere thanks for your support.

Thanks and regards,
gm2612

venkat1926 · Answer

one or two of your requirements are not clearly for e.g. copying format of previ sheet

take a new workbook .
 copy this macro in the vbeditor
SAVE AS MACRO ENABLED WORKBOOK (GIVE SOME NAME)
close the workbook and again open and enable macros

select sheet1
enter A in B5
now run macro test
see what happens.
if this more or less does what you want you can modify to suit you

Sub test()Dim nname As StringWith Worksheets("sheet1")nname = .Range("B5")If .Range("G1").EntireColumn.Find(what:=nname, lookat:=xlWhole) Is Nothing ThenWorksheets.AddActiveSheet.Name = nname.Cells(Rows.Count, "G").End(xlUp).Offset(1, 0) = nnameEnd IfEnd WithActiveWorkbook.SaveEnd Sub

gm2612 · Answer

Hello Venkat,

Many thanks for the reply. However, I get this error

- Compilr Error: Syntax Error

This marks to the line - If .Range("G1").EntireColumn.Find(what:=nname, lookat:=xlWhole) Is Nothing

Basically, my work is to create a database for each customer. Initially I will have few customers, and one data sheet per customer. If the customer name entered in the cell B5 is existing in the column G, then excel should take me to the specific data sheet of this customer. If the customer name is not present in the column G, then it shuold add it in column G, and create a new sheet for this new customer, and copy paste the format from already existing sheet (Say SheetCustA, which is existing customer data sheet).

Can you please help in this.

With Warm Regards,
gm2612

gm2612 · Answer

Hello Venkat,

I am sorry, I missed in aligning your codes properly in the vb editor. After re-aligning, it works perfectly. 

I will further finetune it to my specific requirement.

Thanks a lot.

With Warm Regards,
gm2612

venkat1926 · Answer

download file from
http://speedy.sh/Eymk9/gm2612.xlsm

in sheet1 I added a in B5
and ran the macro 
a new wsheeet a has been added and also a is added to G column(G2)

now you type b in B5 and run the macro what happens

gm2612 · Answer

Dear Venkat,

Many Many thanks.  However, I could not immediately use the xlsm file you have sent because, I need the support of the network admin for it.

Can you please suggest me how to make it an event macro? because I am going to give this work book for a different department, where they will not go the vb editor and press the run button every time.

I am sorry for keep on disturbing you.

Thanks and regards,
gm2612

venkat1926 · Answer

this event code you can try

right click the tab of sheet1 and click view code and in the window that comes up copy this event code


Private Sub Worksheet_Change(ByVal Target As Range)If Target.Address <> "$B$5" Then GoTo exitingOn Error GoTo exitingApplication.EnableEvents = Falsetestexiting:Application.EnableEvents = TrueEnd Sub


now in B5 just enter b or c or any mentry and hit enter key (automatically the macro test is run)

Macro for creating a new excel sheet based on certain conditions

6 responses

Similar discussions

Subscribe To Our Newsletter!