Macro for creating a new excel sheet based on certain conditions
Closed
gm2612
Posts
12
Registration date
Monday December 23, 2013
Status
Member
Last seen
January 1, 2014
-
Dec 24, 2013 at 12:42 AM
gm2612 Posts 12 Registration date Monday December 23, 2013 Status Member Last seen January 1, 2014 - Dec 25, 2013 at 04:54 AM
gm2612 Posts 12 Registration date Monday December 23, 2013 Status Member Last seen January 1, 2014 - Dec 25, 2013 at 04:54 AM
Related:
- Excel create sheets based on column
- Create skype account with gmail - Guide
- Excel macro to create new sheet based on value in cells - Guide
- Create snapchat account - Guide
- Create instagram account on pc - Guide
- Create a yahoo email account for free - Guide
6 responses
venkat1926
Posts
1863
Registration date
Sunday June 14, 2009
Status
Contributor
Last seen
August 7, 2021
811
Dec 24, 2013 at 10:51 PM
Dec 24, 2013 at 10:51 PM
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
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
venkat1926
Posts
1863
Registration date
Sunday June 14, 2009
Status
Contributor
Last seen
August 7, 2021
811
Dec 25, 2013 at 02:50 AM
Dec 25, 2013 at 02:50 AM
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
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
Posts
12
Registration date
Monday December 23, 2013
Status
Member
Last seen
January 1, 2014
Dec 25, 2013 at 02:33 AM
Dec 25, 2013 at 02:33 AM
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
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
Posts
12
Registration date
Monday December 23, 2013
Status
Member
Last seen
January 1, 2014
Dec 25, 2013 at 02:43 AM
Dec 25, 2013 at 02:43 AM
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
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
Didn't find the answer you are looking for?
Ask a question
gm2612
Posts
12
Registration date
Monday December 23, 2013
Status
Member
Last seen
January 1, 2014
Dec 25, 2013 at 02:57 AM
Dec 25, 2013 at 02:57 AM
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
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
Posts
1863
Registration date
Sunday June 14, 2009
Status
Contributor
Last seen
August 7, 2021
811
Dec 25, 2013 at 03:41 AM
Dec 25, 2013 at 03:41 AM
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
now in B5 just enter b or c or any mentry and hit enter key (automatically the macro test is run)
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)
gm2612
Posts
12
Registration date
Monday December 23, 2013
Status
Member
Last seen
January 1, 2014
Dec 25, 2013 at 04:54 AM
Dec 25, 2013 at 04:54 AM
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
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
