Results 1 to 6 of 6

Thread: VBA to create Worksheets from Template
Thanks Thanks: 0 Likes Likes: 0

  1. #1
    New Member
    Join Date
    Jan 2012
    Posts
    37
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    1 Thread(s)

    Default VBA to create Worksheets from Template

    I have a workbook with 2 worksheets. The first worksheet is named "COURSEID" with a list of course IDs, the second worksheet is named "Template" that has formulas. I would like to copy the Template worksheet, and name the copies from a list in COURSEID from A2:A77.

    The VBA code below is not naming the worksheets, and it's placing the COURSEID list on each of the copied sheets.

    Any suggestions?

    Code:
    Sub AddSheets()    Dim xRg As Excel.Range
        Dim wSh As Excel.Worksheet
        Dim sh1 As Excel.Worksheet
        Dim wBk As Excel.Workbook
        Set wSh = Sheets("COURSEID")
        Set sh1 = Sheets("Template")
        Set wBk = ActiveWorkbook
        Application.ScreenUpdating = False
        For Each xRg In wSh.Range("A2:A77")
            With wBk
                sh1.Copy After:=Sheets(Sheets.Count)
                On Error Resume Next
                ActiveSheet.Name = xRg.Value: wSh.Range("A2:A77") = xRg.Value: ActiveSheet.Range("a1") = xRg.Value
                If Err.Number = 1004 Then
                  Debug.Print xRg.Value & " already used as a sheet name"
                End If
                On Error GoTo 0
            End With
        Next xRg
        Application.ScreenUpdating = True
    End Sub

  2. #2
    MrExcel MVP AlphaFrog's Avatar
    Join Date
    Sep 2009
    Posts
    16,087
    Post Thanks / Like
    Mentioned
    16 Post(s)
    Tagged
    7 Thread(s)

    Default Re: VBA to create Worksheets from Template

    Delete the part in red.

    Code:
    Sub AddSheets()    Dim xRg As Excel.Range
        Dim wSh As Excel.Worksheet
        Dim sh1 As Excel.Worksheet
        Dim wBk As Excel.Workbook
        Set wSh = Sheets("COURSEID")
        Set sh1 = Sheets("Template")
        Set wBk = ActiveWorkbook
        Application.ScreenUpdating = False
        For Each xRg In wSh.Range("A2:A77")
            With wBk
                sh1.Copy After:=Sheets(Sheets.Count)
                On Error Resume Next
                ActiveSheet.Name = xRg.Value: wSh.Range("A2:A77") = xRg.Value: ActiveSheet.Range("a1") = xRg.Value
                If Err.Number = 1004 Then
                  Debug.Print xRg.Value & " already used as a sheet name"
                End If
                On Error GoTo 0
            End With
        Next xRg
        Application.ScreenUpdating = True
    End Sub
    Paste your Excel data to the forum...
    MrExcel HTML Maker or Excel Jeanie

    How to post your vba code
    [CODE]your VBA code here[/CODE]
    The # button in the forum's editor will apply CODE tags around your selected text.

  3. #3
    New Member
    Join Date
    Jan 2012
    Posts
    37
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    1 Thread(s)

    Default Re: VBA to create Worksheets from Template

    AlphaFrog, that helped! The worksheets are being created, but only the first worksheet is being named with the corresponding A2 cell in the COURSEID worksheet. The rest of the worksheets are named Template (2), Template (3), etc. In each of the new worksheets, cells A2:A7 are being overwritten with blank cells.

  4. #4
    New Member
    Join Date
    Jan 2012
    Posts
    37
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    1 Thread(s)

    Default Re: VBA to create Worksheets from Template

    Quote Originally Posted by daveasu View Post
    AlphaFrog, that helped! The worksheets are being created, but only the first worksheet is being named with the corresponding A2 cell in the COURSEID worksheet. The rest of the worksheets are named Template (2), Template (3), etc. In each of the new worksheets, cells A2:A7 are being overwritten with blank cells.
    Actually it's overwriting A7:A77 sorry for the typo. Any suggestions on what I may be doing wrong? Many thanks.

  5. #5
    MrExcel MVP AlphaFrog's Avatar
    Join Date
    Sep 2009
    Posts
    16,087
    Post Thanks / Like
    Mentioned
    16 Post(s)
    Tagged
    7 Thread(s)

    Default Re: VBA to create Worksheets from Template

    Code:
    Sub AddSheets()
        Dim xRg       As Excel.Range
        Application.ScreenUpdating = False
        For Each xRg In Sheets("COURSEID").Range("A2:A77")
            Sheets("Template").Copy After:=Sheets(Sheets.Count)
            ActiveSheet.Name = xRg.Value
            ActiveSheet.Range("A1") = xRg.Value
        Next xRg
        Application.ScreenUpdating = True
    End Sub
    Last edited by AlphaFrog; Sep 24th, 2019 at 10:56 PM.
    Paste your Excel data to the forum...
    MrExcel HTML Maker or Excel Jeanie

    How to post your vba code
    [CODE]your VBA code here[/CODE]
    The # button in the forum's editor will apply CODE tags around your selected text.

  6. #6
    New Member
    Join Date
    Jan 2012
    Posts
    37
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    1 Thread(s)

    Default Re: VBA to create Worksheets from Template

    THANK YOU AlphaFrog! That is just what I needed. This process is now working perfectly. (From a fellow Michigan J fan)

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •