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.
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.
