Duplicate template to multiple sheets and save as multiple files from list

[User20813]

Hi,

I am looking to create a Macro that will look at a list from sheet 'Data'

Case owner review
001 a Y
002 b Y
003 a Y
004 c Y
005 c N
006 b Y
007 a Y

Then duplicate sheet 'Template' the number of case numbers under an owners name marked for review, place the Case name in A1 of each sheet, name the sheet the Case number, and save the file as 'review - owner dd/mm/yy' and do this for each owner.

So File 'Review - a dd/mm/yy' would have 3 named sheets with case number in A1
b would have 2 and c would have 1

 

[DanteAmor]

Hi and welcome to MrExcel!

Assume that the data on the "DATA" sheet looks like this:

Dante Amor
	A	B	C
1	Case	owner	review
2	001	a	Y
3	002	b	Y
4	003	a	Y
5	004	c	Y
6	005	c	N
7	006	b	Y
8	007	a	Y
9
Data

Notes:
The files are saved in the same folder where you have the file with the macro.
The file name cannot contain the character "/" therefore the date is: "dd-mm-yy"

Try:

Sub Duplicate_Template()
  Dim sh1 As Worksheet, sh2 As Worksheet, wb2 As Workbook, dic As Object
  Dim ky  As Variant, vCases As Variant, i As Long, a As Variant
  
  Application.ScreenUpdating = False
  Application.DisplayAlerts = False
  
  Set sh1 = Sheets("Data")
  Set sh2 = Sheets("Template")
  Set dic = CreateObject("Scripting.Dictionary")
  dic.CompareMode = vbTextCompare
  a = sh1.Range("A2:C" & sh1.Range("A" & Rows.Count).End(3).Row)
  
  For i = 1 To UBound(a)
    If UCase(a(i, 3)) = "Y" Then dic(a(i, 2)) = dic(a(i, 2)) & "|" & a(i, 1)
  Next
  
  For Each ky In dic.keys
    Set wb2 = Workbooks.Add(xlWBATWorksheet)
    vCases = Split(dic(ky), "|")
    For i = 1 To UBound(vCases)
      sh2.Copy after:=wb2.Sheets(wb2.Sheets.Count)
      wb2.Sheets(wb2.Sheets.Count).Name = vCases(i)
      wb2.Sheets(vCases(i)).Range("A1") = "'" & vCases(i)
    Next
    wb2.Sheets(1).Delete
    wb2.SaveAs ThisWorkbook.Path & "\" & "Review - " & ky & " " & Format(Date, "dd-mm-yy") & ".xlsx"
    wb2.Close False
  Next
  
  MsgBox "End"
  Application.ScreenUpdating = True
End Sub

 

[User20813]

That worked brilliantly. Currently W4:W14 of the template are Vlookups to data on the original file. What can I add to the macro to make just that range values with the correct data instead of formulas. I am worried the formuals will blank out once the files get emailed out. Thank you so much for the help.

 

[DanteAmor]

Try this

After this line

wb2.Sheets(vCases(i)).Range("A1") = "'" & vCases(i)

Put this line:

wb2.Sheets(vCases(i)).Range("W4:W14").value = wb2.Sheets(vCases(i)).Range("W4:W14").value