Creating new sheet when got into some event [Closed]

Report
Posts
4
Registration date
Thursday September 3, 2015
Status
Member
Last seen
October 12, 2015
-
Posts
2776
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
July 26, 2021
-

Please help me built macro like this :
When i write name at "C7" and pressing Enter Button it will create new Sheet with the C7.Value.And it will do same at "C8""C9""C10" and looping each time i pressing enter after write the name.

2 replies

Posts
2776
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
July 26, 2021
466
Hi Ken,

Try the following code:
Private Sub Worksheet_Change(ByVal Target As Range)
If Intersect(Target, Columns("C:C")) Is Nothing Then Exit Sub
Sheets.Add After:=Sheets(Sheets.Count)
ActiveSheet.Name = Target.Value
End Sub


Implement code by right-clicking the sheets tab and select View code, then paste the code in the big white field.

Best regards,
Trowa
1
Thank you

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

CCM 2942 users have said thank you to us this month

Posts
4
Registration date
Thursday September 3, 2015
Status
Member
Last seen
October 12, 2015

Dear TrowaD,

Thanks for your help man, your guide help me to overcome my long lasting work :D

Best Regard
Ken -
Posts
4
Registration date
Thursday September 3, 2015
Status
Member
Last seen
October 12, 2015

Dear

Can you help me again

I have some master sheet with some format inside the sheet
here's what my point

After create new sheet (when pressing enter) the new sheet copying the master sheet and make the name hyperlink to newsheet

Ex : I input name Joshua at C7 then create new sheet with the name of Joshuan but at the same time new sheet (joshua sheet) copying from master sheet, and at the C7 where i input joshua beeing hyperlink to the sheet with joshua name / what created.

Thanks for everything, btw where i can learn all those things, so maybe i can build my self and give anybody advice too.
Posts
2776
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
July 26, 2021
466
Hi Ken,

Not entirely sure if I understand what you want to achieve ...

The code below will do the following:
After entering a value in Column C, the Master sheet will be copied and renamed after the entered value. A hyperlink is then added, so that when the entered value is clicked on, the newly copied renamed master sheet will be selected.

OR more specific:

When Joshua is entered in C7, Master sheet will be copied and renamed Joshua. C7 will be hyperlinked to sheet Joshua.

Here is the code:
Private Sub Worksheet_Change(ByVal Target As Range)
If Intersect(Target, Columns("C:C")) Is Nothing Then Exit Sub
Target.Hyperlinks.Add Anchor:=Target, Address:="", _
SubAddress:=Target.Value & "!A1", TextToDisplay:=Target.Value
Sheets("Master").Copy After:=Sheets(Sheets.Count)
ActiveSheet.Name = Target.Value
End Sub


As for learning this for yourself, I use trial & error, vba recorder, google and of course Kioskea.

Good luck and best regards,
Trowa

Subscribe To Our Newsletter!

The Best of CCM in Your Inbox

Subscribe To Our Newsletter!