Macro for creating a new excel sheet based on certain conditions

[Closed]
Report
Posts
12
Registration date
Monday December 23, 2013
Status
Member
Last seen
January 1, 2014
-
Posts
12
Registration date
Monday December 23, 2013
Status
Member
Last seen
January 1, 2014
-
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

6 replies

Posts
1864
Registration date
Sunday June 14, 2009
Status
Contributor
Last seen
August 7, 2021
803
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 String
With Worksheets("sheet1")
nname = .Range("B5")
If .Range("G1").EntireColumn.Find(what:=nname, lookat:=xlWhole) Is Nothing Then
Worksheets.Add
ActiveSheet.Name = nname
.Cells(Rows.Count, "G").End(xlUp).Offset(1, 0) = nname
End If
End With
ActiveWorkbook.Save



End Sub
1
Thank you

A few words of thanks would be greatly appreciated. Add comment

CCM 2821 users have said thank you to us this month

Posts
1864
Registration date
Sunday June 14, 2009
Status
Contributor
Last seen
August 7, 2021
803
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
1
Thank you

A few words of thanks would be greatly appreciated. Add comment

CCM 2821 users have said thank you to us this month

Posts
12
Registration date
Monday December 23, 2013
Status
Member
Last seen
January 1, 2014

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
Posts
12
Registration date
Monday December 23, 2013
Status
Member
Last seen
January 1, 2014

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
Posts
12
Registration date
Monday December 23, 2013
Status
Member
Last seen
January 1, 2014

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
Posts
1864
Registration date
Sunday June 14, 2009
Status
Contributor
Last seen
August 7, 2021
803
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 exiting
On Error GoTo exiting
Application.EnableEvents = False
test
exiting:
Application.EnableEvents = True


End Sub


now in B5 just enter b or c or any mentry and hit enter key (automatically the macro test is run)
Posts
12
Registration date
Monday December 23, 2013
Status
Member
Last seen
January 1, 2014

Thank you Venkat. It is working fantastically. My million salutes to you.

Working on macro for copying format from one sheet to another. Will seek your help if required.

Wish you happy new year 2014.

Regards,
gm2612