Autogenerate worksheets from a list of names... Adding names?

[Closed]
Report
-
Posts
2783
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
August 3, 2021
-
Hi, I'm trying to auto-generate worksheet from a list of names. Reading other questions in the forum I found the solution for this questions. But I update the list of names in a daily basis. I need that each time I update the list, excel auto-generate a new worksheet.
This is what i'm using now...

Sub CreateSheetsFromAList()
Dim MyCell As Range, MyRange As Range

Set MyRange = Sheets("Summary").Range("A3")
Set MyRange = Range(MyRange, MyRange.End(xlDown))

For Each MyCell In MyRange
Sheets(2).Copy After:=Sheets(Sheets.Count) 'Create a new worksheet as a copy of Sheet number 2
Sheets(Sheets.Count).Name = MyCell.Value ' renames the new worksheet
Next MyCell
End Sub

Please help! I'm not a programmer, I'm an accountant trying to solve an issue for a non for profit organization.

Thanks!

1 reply

Posts
2783
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
August 3, 2021
468
Hi Juliza,

Whenever a change is made in column A, then that value will be used to name the new sheet.

To implement the following code, right-click your sheet (with the list of sheet names) and select view code, then paste the code below:
Private Sub Worksheet_Change(ByVal Target As Range)
If Intersect(Target, Range("A:A")) Is Nothing Then Exit Sub
Sheets(2).Copy After:=Sheets(Sheets.Count)
Sheets(Sheets.Count).Name = Target.Value
End Sub


Let us know if this works for you and/or if anything comes up.

Best regards,
Trowa

Subscribe To Our Newsletter!

The Best of CCM in Your Inbox

Subscribe To Our Newsletter!