Asking for a different sheet name if current input already exists

[Jarlisle]

I have a document that I want to copy the values and formats in certain columns and take that information and post to a new sheet. I want the macro to ask for the new sheet name, but if the name given is already in the workbook then I want to prompt for a new name.

Here's what I have so far, but I can't figure out how to check the sheet names and prompt for a new one if the given one exists.



Application.ScreenUpdating = False
Dim NewName As String
Sheets("Exec Summary").Select
Columns("A:F").Copy
Sheets.Add Before:=ActiveSheet
NewName = InputBox("What date is this sheet for? (Use period to separate months, days & 2 digit years)")
ActiveSheet.Name = NewName
Selection.PasteSpecial Paste:=xlPasteValuesAndNumberFormats, Operation:= xlNone, SkipBlanks:=False, Transpose:=False
Selection.PasteSpecial Paste:=xlPasteFormats, Operation:=xlNone, SkipBlanks:=False, Transpose:=False
Application.ScreenUpdating = True
End Sub

[GarysStudent]

Ask for the new name before adding the sheet:


Sub AddASheet()
Dim NewName As String, IsNameGood As Boolean
IsNameGood = False
While Not IsNameGood
NewName = Application.InputBox(Prompt:="Give me the new name", Type:=2)
IsNameGood = True
For Each sh In Sheets
If NewName = sh.Name Then
IsNameGood = False
End If
Next
Wend
Sheets.Add Before:=ActiveSheet
ActiveSheet.Name = NewName
End Sub