That should work:

Sub CreateSheets()
Dim ws_list As Worksheet
Dim ws_template As Worksheet
Dim ws_new As Worksheet
Dim uniqueColumn As Range
Dim lastRow As Integer
Dim startRow As Integer
Dim cell_ As Range
Dim count_ As Integer

Set ws_list = Sheets("sheet1")
Set ws_template = Sheets("sheet2")
lastRow = ws_list.Cells(65536, 1).End(xlUp).Row
startRow = 2
Set uniqueColumn = ws_list.Range(ws_list.Cells(startRow, 1),
ws_list.Cells(lastRow, 1))

count_ = 2

For Each cell_ In uniqueColumn
If Not WksExists(cell_.Value) Then
ws_template.Copy After:=Sheets(Sheets.count)
Set ws_new = ActiveSheet
With ws_new
.Name = count_
ws_new.Hyperlinks.Add Anchor:=.Range("A1"), _
Address:="", SubAddress:="Sheet1!A1", _
TextToDisplay:=cell_.Value
End With
count_ = count_ + 1
End If
Next cell_

End Sub

you cannot set it to startrow, because this value never changes.

hth
Carlo


On Jan 11, 6:46*am, gh0st wrote:
On 10 Jan, 19:10, carlo wrote:



Try this sub:

Sub CreateSheets()
Dim ws_list As Worksheet
Dim ws_template As Worksheet
Dim ws_new As Worksheet
Dim uniqueColumn As Range
Dim lastRow As Integer
Dim startRow As Integer
Dim cell_ As Range

Set ws_list = Sheets("list")
Set ws_template = Sheets("template")
lastRow = ws_list.Cells(65536, 1).End(xlUp).Row
startRow = 2
Set uniqueColumn = ws_list.Range(ws_list.Cells(startRow, 1),
ws_list.Cells(lastRow, 1))

For Each cell_ In uniqueColumn
* * If Not WksExists(cell_.Value) Then
* * * * ws_template.Copy After:=Sheets(Sheets.Count)
* * * * Set ws_new = ActiveSheet
* * * * ws_new.Name = cell_ '
* * * * ws_new.Range("A1") = cell_
* * End If
Next cell_

End Sub

Function WksExists(wksName As String) As Boolean
* * On Error Resume Next
* * WksExists = CBool(Len(Worksheets(wksName).Name) 0)
End Function

hth

Carlo

Hi Carlo,

This is a great start thanks. Works very well, but the sheet names are
named with the cell data, whereas I wanted to name the pages by
numbers 2, 3, 4, etc
Seems to be this part of the code..
ws_new.Name = cell_ '

I tired
ws_new.Name = startRow '
but this creates only the first sheet (numbered 2) then an error
occurs.

I also wanted to have the data name at A1 hyperlinked back to the list
sheet, is this possible?

Kindest Regards
gh0st- Hide quoted text -

- Show quoted text -