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

Question

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!

TrowaD · Answer

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

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

1 response

Similar discussions

Subscribe To Our Newsletter!