Creating new sheet when got into some event

Closed
ken.konzu
Posts
4
Registration date
Thursday September 3, 2015
Status
Member
Last seen
October 12, 2015
- Sep 3, 2015 at 04:45 AM
TrowaD
Posts
2900
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
September 12, 2022
- Sep 7, 2015 at 11:46 AM

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

TrowaD
Posts
2900
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
September 12, 2022
523
Sep 3, 2015 at 11:02 AM
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
ken.konzu
Posts
4
Registration date
Thursday September 3, 2015
Status
Member
Last seen
October 12, 2015

Sep 3, 2015 at 11:24 PM
Dear TrowaD,

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

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

Sep 4, 2015 at 12:29 AM
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.
0
TrowaD
Posts
2900
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
September 12, 2022
523
Sep 7, 2015 at 11:46 AM
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
0