Duplicate, rename sheet but not if it already exist..

[buzz71023]

I have this code that duplicated my main sheet and renames is based on a cell value. My problem I don't want to duplicate the sheet if it already exist, which is going to happen A LOT. I need help making this happen. Below is what I have so far.

Dim MySheetName As String 
    MySheetName = "TestSheet" 
    Sheets("MasterSheet").Copy After:=Sheets("MasterSheet") 
    ActiveSheet.Name = MySheetName

 

[Peter_SSs]

Try

Dim MySheetName As String
Dim wsCheck As Worksheet

    MySheetName = "TestSheet"
    On Error Resume Next
    Set wsCheck = Sheets(MySheetName)
    On Error GoTo 0
    If wsCheck Is Nothing Then
      Sheets("MasterSheet").Copy After:=Sheets("MasterSheet")
      ActiveSheet.Name = MySheetName
    End If

 

[My Aswer Is This]

What cell has the sheet name assigned to it. How does the cell get it's value.

 

[buzz71023]

That worked perfectly! Is there a way to get around the prompt for existing name ranges. I always would click yes.

 

[buzz71023]

sorry this is actually what I originally had. I didn't realize I posted one from the research I was doing.

Dim NameSheet As String

Application.ScreenUpdating = False
    NameSheet = Sheets("Data Sheet").Range("ProductCode").Value
    Sheets("Data Sheet").Copy After:=Sheets("110060-B")
    ActiveSheet.Name = NameSheet
    
Sheets("Data Sheet").Select
Application.ScreenUpdating = True

 

[Peter_SSs]

Is there a way to get around the prompt for existing name ranges. I always would click yes.

I'm not sure of the circumstances here but try

Application.DisplayAlerts = False
  'Other code here
Application.DisplayAlerts = True

 

[buzz71023]

Thanks, works just like I wanted.

To help anyone who may have these same questions later, here was my final code.

Dim MySheetName As String
Dim wsCheck As Worksheet

Application.ScreenUpdating = False
Application.DisplayAlerts = False
        
    MySheetName = Sheets("Data Sheet").Range("ProductCode").Value
    On Error Resume Next
    Set wsCheck = Sheets(MySheetName)
    On Error GoTo 0
    If wsCheck Is Nothing Then
      Sheets("Data Sheet").Copy After:=Sheets("110060-B")
      ActiveSheet.Name = MySheetName
    End If
    
     Sheets("Data Sheet").Select

Application.ScreenUpdating = True
Application.DisplayAlerts = True