Problem with duplicates

paulsolar

Well-known Member
Joined
Aug 21, 2013
Messages
670
Office Version
  1. 365
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
 

Excel Facts

When did Power Query debut in Excel?
Although it was an add-in in Excel 2010 & Excel 2013, Power Query became a part of Excel in 2016, in Data, Get & Transform Data.
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
 
Upvote 0
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
 
Upvote 0
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.
 
Upvote 0
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:
Upvote 0
neglected to mention i moved the info for the new sheet names from column B to column A on the tides sheet
 
Last edited:
Upvote 0
What is the error message?
 
Upvote 0
it is creating the first two worksheets and copying the template to them
 
Upvote 0
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.
 
Upvote 0

Forum statistics

Threads
1,213,490
Messages
6,113,956
Members
448,535
Latest member
alrossman

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top