Problem with duplicates

paulsolar

Well-known Member
Joined
Aug 21, 2013
Messages
625
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.

Code:
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
 

Some videos you may like

Excel Facts

Shade all formula cells
To shade all formula cells: Home, Find & Select, Formulas to select all formulas. Then apply a light fill color.

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
35,860
Office Version
365
Platform
Windows
How about
Code:
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

Well-known Member
Joined
Aug 21, 2013
Messages
625
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

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
35,860
Office Version
365
Platform
Windows
How about
Code:
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

Well-known Member
Joined
Aug 21, 2013
Messages
625
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

Code:
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
 
Last edited:

paulsolar

Well-known Member
Joined
Aug 21, 2013
Messages
625
neglected to mention i moved the info for the new sheet names from column B to column A on the tides sheet
 
Last edited:

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
35,860
Office Version
365
Platform
Windows
What is the error message?
 

paulsolar

Well-known Member
Joined
Aug 21, 2013
Messages
625
it is creating the first two worksheets and copying the template to them
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
35,860
Office Version
365
Platform
Windows
Try
Code:
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,089,945
Messages
5,411,443
Members
403,370
Latest member
Massi_921

This Week's Hot Topics

Top