Problem with duplicates

[paulsolar]

Hi All

I'm using the code below to create template copies, how can i adapt it to avoid duplicates please and only create unique worksheets.

Sub NewDays()


Dim i As Long, LastRow As Long, wksht As Worksheet


Sheets("Tides").Activate


LastRow = Cells(Rows.Count, 1).End(xlUp).Row


For i = 1 To LastRow
    Sheets("Template").Copy After:=Sheets(i)
    ActiveSheet.Name = Cells(i, 1).Text
Next i


End Sub

Cheers

Paul

 

[Fluff]

How about

Sub NewDays()


Dim i As Long, LastRow As Long, wksht As Worksheet


Sheets("Tides").Activate


LastRow = Cells(Rows.count, 1).End(xlUp).Row


For i = 1 To LastRow
   If Not Evaluate("isref('" & Cells(i, 1).Text & "'!A1)") Then
      Sheets("Template").Copy After:=Sheets(i)
      ActiveSheet.Name = Cells(i, 1).Text
   End If
Next i


End Sub

 

[paulsolar]

Hi Fluff

come on you reds(we were lucky)

Many thanks for your reply as always, your code is starting to work and creates a few sheets but then has a run time error 9

Any ideas on what that would be? I'm checking the format of column a to ensure that the format is consistent

cheers

Paul

 

[Fluff]

How about

Sub NewDays()


Dim i As Long, LastRow As Long, wksht As Worksheet


With Sheets("Tides")
   
   
   LastRow = .Cells(Rows.count, 1).End(xlUp).Row
   
   
   For i = 1 To LastRow
      If Not Evaluate("isref('" & .Cells(i, 1).Text & "'!A1)") Then
         Sheets("Template").Copy After:=Sheets(i)
         ActiveSheet.Name = .Cells(i, 1).Text
      End If
   Next i
End With

End Sub

At least you made it through, looks as both England & Scotland's games may be cancelled.

 

[paulsolar]

i thought they were moving the matches for England and Scotland matches to a different venue, i didn't realise they were going to cancel them, thats a shock. Apparently both teams get 2 points if the match is cancelled, would Scotland get through with 2 points? I've lost tough with everything until this morning.

I kicked off originally with the code below, to create the worksheets and it worked but i needed to copy the template into each new worksheet created that was where i fell apart.

i'm still having the same error code 9

Sub CreateSheets()    Dim c As Range, sh As Worksheet, s As String
    Application.ScreenUpdating = False
    With ActiveSheet
        For Each c In .Range(.Range("B1"), .Range("B" & .Rows.Count).End(xlUp))
            s = c.Text
            If s <> "" Then
                On Error Resume Next
                Set sh = Sheets(s)
                On Error GoTo 0
                If sh Is Nothing Then
                    Set sh = Sheets.Add(After:=Sheets(Sheets.Count))
                    sh.Name = s
                End If
                Set sh = Nothing
            End If
        Next c
    End With
    Application.ScreenUpdating = True
End Sub

 

[paulsolar]

neglected to mention i moved the info for the new sheet names from column B to column A on the tides sheet

 

[Fluff]

What is the error message?

 

[paulsolar]

runtime error 9

subscript out of range

 

[paulsolar]

it is creating the first two worksheets and copying the template to them

 

[Fluff]

Try

Sheets("Template").Copy After:=Sheets([COLOR=#ff0000]Sheets.count[/COLOR])

Or change the part in red to 1 if you want to put them at the start, rather than the end.