How to stop the cancel button from carrying out the sub?

[Balrajss0121]

Hi, I am fairly new to vba and am trying to work through a spreadsheet. At the moment I have managed to create a macro via vba as shown below. It currently works via pressing the required button and an input box being displayed which asks for the name of the new sheet to be created. The new sheet is copied from a hidden sheet called "MASTER SHEET". The issue I am having is that when I press cancel or the close button on the input box, a new sheet is still created given the name MASTER SHEET(1) etc. Any help would be appreciated in rectifying the issue

The code is as follows:

Sub CopySheetAndRename()

Dim ActNm As String
Application.ScreenUpdating = False
On Error Resume Next
ActiveWorkbook.Sheets("MASTER SHEET").Visible = True
ActiveWorkbook.Sheets("MASTER SHEET").Copy _
after:=ActiveWorkbook.Sheets("MASTER SHEET")
ActNm = ActiveSheet.Name
ActiveSheet.Name = InputBox("Enter the name for the new sheet.")
Sheets(ActiveSheet.Name).Visible = True
ActiveWorkbook.Sheets("MASTER SHEET").Visible = False
Application.ScreenUpdating = True

End Sub

 

[Fluff]

Hi & welcome to MrExcel.
How about

Sub CopySheetAndRename()
   Dim ActNm As String
   
   ActNm = InputBox("Enter the name for the new sheet.")
   If ActNm = "" Then Exit Sub
   Application.ScreenUpdating = False
   With ActiveWorkbook
      .Sheets("MASTER SHEET").Visible = True
      .Sheets("MASTER SHEET").Copy , .Sheets("MASTER SHEET")
      ActiveSheet.Name = ActNm
      .Sheets("MASTER SHEET").Visible = False
   End With
   Application.ScreenUpdating = True
End Sub

 

[Balrajss0121]

Hi & welcome to MrExcel.
How about

Sub CopySheetAndRename()
   Dim ActNm As String
  
   ActNm = InputBox("Enter the name for the new sheet.")
   If ActNm = "" Then Exit Sub
   Application.ScreenUpdating = False
   With ActiveWorkbook
      .Sheets("MASTER SHEET").Visible = True
      .Sheets("MASTER SHEET").Copy , .Sheets("MASTER SHEET")
      ActiveSheet.Name = ActNm
      .Sheets("MASTER SHEET").Visible = False
   End With
   Application.ScreenUpdating = True
End Sub

Thank you so much, that worked a charm. My only other query which I just stumbled upon is to stop an error being thrown when a duplicate name is used as instead tell the user to use another name. I appreciate your help greatly

 

[Fluff]

Ok, how about

Sub CopySheetAndRename()
   Dim ActNm As String
   
   ActNm = InputBox("Enter the name for the new sheet.")
   If ActNm = "" Then Exit Sub
   If Evaluate("isref('" & ActNm & "'!A1)") Then
      MsgBox "That name is taken"
      Exit Sub
   End If
   Application.ScreenUpdating = False
   With ActiveWorkbook
      .Sheets("MASTER SHEET").Visible = True
      .Sheets("MASTER SHEET").Copy , .Sheets("MASTER SHEET")
      ActiveSheet.Name = ActNm
      .Sheets("MASTER SHEET").Visible = False
   End With
   Application.ScreenUpdating = True
End Sub

 

[Balrajss0121]

Ok, how about

Sub CopySheetAndRename()
   Dim ActNm As String
  
   ActNm = InputBox("Enter the name for the new sheet.")
   If ActNm = "" Then Exit Sub
   If Evaluate("isref('" & ActNm & "'!A1)") Then
      MsgBox "That name is taken"
      Exit Sub
   End If
   Application.ScreenUpdating = False
   With ActiveWorkbook
      .Sheets("MASTER SHEET").Visible = True
      .Sheets("MASTER SHEET").Copy , .Sheets("MASTER SHEET")
      ActiveSheet.Name = ActNm
      .Sheets("MASTER SHEET").Visible = False
   End With
   Application.ScreenUpdating = True
End Sub

Thanks that worked perfectly too. Is there any chance you could explain the code you just added for the duplicate name, so I understand fully for next time

 

[Fluff]

It uses the worksheet function ISREF to test if cell A1 exists on that sheet (ie does the sheet exist). The evaluate allows VBA to calculate a string formula & will return true or false depending on whether the sheet exists.

 

[Balrajss0121]

Okay thank you very much. You helped me a lot

 

[Fluff]

You're welcome & thanks for the feedback.