How to automatically add a worksheet from a template when a cell is filled out on a master sheet?

Question

I have an excel workbook with two worksheets "Master" and "Template" I want to copy "template" once a cell, from range A2:A20, in worksheet "Master" is filled out and name a new worksheet with A2:20 value. I would like to copy value from Master sheet of two cells, into newly created worksheet (from master cells A2:B2 to new sheet (with a name A2) to cells D1:D2). I want to do that for each cells A2:A20 that are filled out, so that it would not create a new worksheet for cells from A2:A20 that are empty.

So if I would have A2, A4, and A5 filled out I would get 3 new worksheet, like template worksheet, with names from A2, A4 and A5. In first new sheet A2:B2 from master, would be copied to D1:D2. In second new sheet A4:B4, would be copied to D1:D2 and on the third new worksheet A5:B5 would be copied to D1:D2

I would like to have this done, once excel is open, and if once done, not to repeat it

is this possible?

much appreciate any help

Sub CreateAndNameWorksheets()

    Dim c As Range

    Application.ScreenUpdating = False

    For Each c In Sheets("Master").Range("A2:A20")

        Sheets("Template").Copy After:=Sheets(Sheets.Count)

        With c

            ActiveSheet.Name = .Value

           .Parent.Hyperlinks.Add Anchor:=c, Address:="", SubAddress:= _
                "'" & .Text & "'!A1", TextToDisplay:=.Text

        End With

    Next c

    Application.ScreenUpdating = True


End Sub

Scott Holtzman Scott Holtzman · Accepted Answer · 2019-06-02T00:00:16

Place this code in the Workbook module of the Excel file.

Option Explicit

Private Sub Workbook_Open()

    With Worksheets("Master")

        'check if sheet creation process has happened already
        If .Cells(1, .Columns.Count).Value = "" Then

            Dim c As Range

            Application.ScreenUpdating = False

            For Each c In .Range("A2:A20")

                Worksheets("Template").Copy After:=Sheets(Sheets.Count)

                With c

                    ActiveSheet.Name = .Value

                    .Parent.Hyperlinks.Add Anchor:=c, Address:="", SubAddress:= _
                         "'" & .Text & "'!A1", TextToDisplay:=.Text

                End With

            Next c

            .Cells(1, .Columns.Count).Value = "created" 'mark cell as created so it skips next time and each time after

        End If

    End With

End Sub

ThisWorkbook class module:

How to automatically add a worksheet from a template when a cell is filled out on a master sheet?

1 Answers