Copy Template, Rename based on a reference list from a different spreadsheetClosed

Question

Hello:
I am trying to make copies of a template in a worksheet, and rename those worksheets from a list on a separate worksheet.
Currently my template sheet is what I want to copy.  The number of copies will be based on a list of dates on a separate worksheet within the same workbook. 

For instance, I need to copy my template (worksheet 1), and need to make 52 copies of the same template. The names of those copies are in worksheet 2, that contains a list 52 weeks and how they should be named ("July 1- July 7, 2018", etc.).  Could you assist with a macro for that. 

I can make copies of the template, and I can make blank new sheets with the names on the list of the reference, but I can't seem to combine the two to create my goal.

vcoolio · Answer

Hello Sublimus,

Let's assume that the new sheet names are in Sheet2, Column A starting in A2 and that you've named your template sheet "Template". Now try the following code, placed in a standard module and assigned to a button:-

Option Explicit

Sub CreateSheetsFromTemplate()

Dim wsT As Worksheet
      Dim wsNames As Range, Rng As Range

Set wsT = Sheets("Template")
      Set wsNames = Sheet2.Range("A2:A" & Rows.Count).SpecialCells(2)
    
Application.ScreenUpdating = False

For Each Rng In wsNames
      If Not Evaluate("ISREF('" & CStr(Rng.Text) & "'!A1)") Then
      wsT.Copy After:=Sheets(Sheets.Count)
      ActiveSheet.Name = CStr(Rng.Text)
      End If
Next Rng

wsT.Select

Application.ScreenUpdating = True

End Sub

This code should do as you ask and create new sheets from your Template sheet and naming them from the list in Column A of Sheet2.

Test this in a copy of your workbook first.

I hope that this helps.

Cheerio,
vcoolio.

Copy Template, Rename based on a reference list from a different spreadsheet

1 response

Similar discussions

Subscribe To Our Newsletter!