Macro to create worksheets from template ws W/ Dynamic range with duplicates in range

[balla506]

Hi,


I am trying to make this macro not use a named range but a dynamic range (so later on if a new customer needs to be added they can and it will create the worksheet). Also there are duplicate customers in the range which is on the "'reference" worksheet. If it sees a duplicate and a worksheet is already created for it, it should skip over to the next one. The customer names are in column F on the reference sheet. Any help is much appreciated. Thanks.

Sub CopySheets()


Dim c As Range


'set calc to manual to save time
    
Application.Calculation = xlCalculationManual




'copies non-rollup Sheets from hierarchy named range


For Each c In Range("hierarchy")


Sheets("Template Scorecard Sheet").Copy Before:=Sheets("Template Scorecard Sheet")
ActiveSheet.Name = c.Value


Next c


Application.Calculation = xlCalculationAutomatic


End Sub

 

[balla506]

Solved...

Sub CopySheets()

Dim bottomA As Long, rngUniques As Range, c As Range


'set calc to manual to save time
    
Application.Calculation = xlCalculationManual


'finds unique values in Name column


bottomF = Range("F" & Rows.Count).End(xlUp).Row
Range("F1:F" & bottomF).AdvancedFilter Action:=xlFilterInPlace, Unique:=True
Set rngUniques = Range("F2:F" & bottomF).SpecialCells(xlCellTypeVisible)
If ActiveSheet.FilterMode Then ActiveSheet.ShowAllData




'creates a sheet for every customer


For Each c In rngUniques


Sheets("Template Scorecard Sheet").Copy Before:=Sheets("Template Scorecard Sheet")
ActiveSheet.Name = c.Value


Next c




Application.Calculation = xlCalculationAutomatic
End Sub