Sub CreateSheetsFromAList_Correct()
Dim MyCell As Range, MyRange As Range
Set MyRange = Sheets("Summary").Range("A8") 'Summary is the basis worksheet and A8 is the starting point
Set MyRange = Range(MyRange, MyRange.End(xlDown))
Sheets("Template").Select 'select the template worksheet
Range("A1:Z149").Select 'select area you needed
Range("Z149").Activate 'range end point
Application.CutCopyMode = False
Selection.Copy 'copy the content
For Each MyCell In MyRange
'creates new worksheet
Sheets.Add After:=Sheets(Sheets.Count) 'creates a new worksheet
Sheets(Sheets.Count).Name = MyCell.Value 'renames the new worksheet
'paste the template to new worksheet
ActiveSheet.[A1].Select 'pastes destination
Selection.PasteSpecial Paste:=xlPasteFormats, Operation:=xlNone, _
SkipBlanks:=False, Transpose:=False ' pastes special column width
ActiveSheet.Paste 'paste the formula and data
'use the worksheet name as key for formula
ActiveSheet.[A150] = ActiveSheet.Name 'create the reference (worksheet name) for vlookup
'autofilter the "v" in column E
ActiveSheet.Range("A1:F150").AutoFilter
ActiveSheet.Range("A1:F150").AutoFilter Field:=5, Criteria1:="<>"
Next MyCell
End Sub
Bookmarks