Duplicate sheets by data count
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
|