Previous Topic Next Topic

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

Closed
Sublimus Posts 1 Registration date Wednesday July 17, 2019 Status Member Last seen July 17, 2019 - Jul 17, 2019 at 07:32 PM
vcoolio Posts 1361 Registration date Thursday July 24, 2014 Status Moderator Last seen September 27, 2022 - Jul 18, 2019 at 02:45 AM
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.
Related:

1 reply

Answer 1 / 1
vcoolio Posts 1361 Registration date Thursday July 24, 2014 Status Moderator Last seen September 27, 2022 250
Jul 18, 2019 at 02:45 AM
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.
0