Excel – VBA Macro to check if sheet name exists

excelvba

I'm currently running a macro that copies a template sheet, takes user's inputs, renames the sheet, and places the user inputs in the sheet.

Everything seems to be working except for the function that checks if the Sheet name already exists. The actual "Sheet already exists" works, however, before prompting me with this error it duplicates the TEMPLATE worksheet for some reason.

Here's the code for the actual macro (affected area is: Sheets("TEMPLATE").Copy After:=Sheets("TEMPLATE"))

'============================================================================
'Button to load new GSA Form
'============================================================================
Private Sub CommandButton1_Click()

Const cstrTitle As String = "Create a new GSA worksheet"
Const cstrPrompt As String = "Enter the GSA number for the new worksheet"
Dim projName As String
Dim projAddress As String
Dim projDate As Date
Dim strInput As Variant                                     'Input value from user (GSA Number)
Dim strDefault As String: strDefault = ""                   'Sets default value for inputbox
Dim strInputErrorMessage As String                          'Error message
Dim booValidateOK As Boolean: booValidateOK = False
On Error GoTo HandleError

Do
    strInput = ActiveSheet.Range("C9").Value
    projName = ActiveSheet.Range("C6").Value
    projAddress = ActiveSheet.Range("C7").Value
    projDate = ActiveSheet.Range("C8").Value

    If Len(strInput) = 0 Then GoTo HandleExit
    GoSub ValidateInput
    If Not booValidateOK Then
        If vbCancel = MsgBox(strInputErrorMessage & "Retry?", vbExclamation + vbOKCancel) Then GoTo HandleExit
    End If
Loop While Not booValidateOK

Sheets("TEMPLATE").Copy After:=Sheets("TEMPLATE")                          'Copy Template Sheet, places the copy after the template sheet
ActiveSheet.Name = strInput                                                 'Renames the new sheet to the user's input

ActiveSheet.Range("C5").Value = projName                                    'Inputs Project Name to new sheet
ActiveSheet.Range("C6").Value = projAddress                                 'Inputs Project Address to new sheet
ActiveSheet.Range("C7").Value = projDate                                    'Inputs Project Date to new sheet
ActiveSheet.Range("C8").Value = strInput                                    'Inputs GSA # to new sheet

ThisWorkbook.Worksheets("MASTER").Range("C6").Value = ""   'name'           'Deletes inputs from MASTER sheet
ThisWorkbook.Worksheets("MASTER").Range("C7").Value = ""   'address'
ThisWorkbook.Worksheets("MASTER").Range("C8").Value = ""   'date'
ThisWorkbook.Worksheets("MASTER").Range("C9").Value = ""   'gsa #'



HandleExit:
Exit Sub
HandleError:
MsgBox Err.Description
Resume HandleExit

ValidateInput:
If SheetExists(strSheetName:=strInput) Then
    strInputErrorMessage = "Sheet already exists. "
Else
    booValidateOK = True
End If
Return
End Sub

Here's the function that checks to see if the sheet already exists

Public Function SheetExists(strSheetName As Variant, Optional wbWorkbook As Workbook) As Boolean
If wbWorkbook Is Nothing Then Set wbWorkbook = ActiveWorkbook           'or ThisWorkbook - whichever appropriate
Dim obj As Object
On Error GoTo HandleError
Set obj = wbWorkbook.Sheets(strSheetName)
SheetExists = True
Exit Function
HandleError:
 SheetExists = False
End Function

Best Answer

Try this :

Function sheetExists(sheetToFind As String) As Boolean
    sheetExists = False
    For Each sheet In Worksheets
        If sheetToFind = sheet.name Then
            sheetExists = True
            Exit Function
        End If
    Next sheet
End Function

And use like this :

if sheetExists("TEMPLATE") = true then
  'your code
else
  'code
end if

Excel VBA If WorkSheet("wsName") Exists

Some examples of how to avoid select

Use Dim'd variables

Dim rng as Range

Set the variable to the required range. There are many ways to refer to a single-cell range:

Set rng = Range("A1")
Set rng = Cells(1, 1)
Set rng = Range("NamedRange")

Or a multi-cell range:

Set rng = Range("A1:B10")
Set rng = Range("A1", "B10")
Set rng = Range(Cells(1, 1), Cells(10, 2))
Set rng = Range("AnotherNamedRange")
Set rng = Range("A1").Resize(10, 2)

You can use the shortcut to the Evaluate method, but this is less efficient and should generally be avoided in production code.

Set rng = [A1]
Set rng = [A1:B10]

All the above examples refer to cells on the active sheet. Unless you specifically want to work only with the active sheet, it is better to Dim a Worksheet variable too:

Dim ws As Worksheet
Set ws = Worksheets("Sheet1")
Set rng = ws.Cells(1, 1)
With ws
    Set rng = .Range(.Cells(1, 1), .Cells(2, 10))
End With

If you do want to work with the ActiveSheet, for clarity it's best to be explicit. But take care, as some Worksheet methods change the active sheet.

Set rng = ActiveSheet.Range("A1")

Again, this refers to the active workbook. Unless you specifically want to work only with the ActiveWorkbook or ThisWorkbook, it is better to Dim a Workbook variable too.

Dim wb As Workbook
Set wb = Application.Workbooks("Book1")
Set rng = wb.Worksheets("Sheet1").Range("A1")

If you do want to work with the ActiveWorkbook, for clarity it's best to be explicit. But take care, as many WorkBook methods change the active book.

Set rng = ActiveWorkbook.Worksheets("Sheet1").Range("A1")

You can also use the ThisWorkbook object to refer to the book containing the running code.

Set rng = ThisWorkbook.Worksheets("Sheet1").Range("A1")

A common (bad) piece of code is to open a book, get some data then close again

This is bad:

Sub foo()
    Dim v as Variant
    Workbooks("Book1.xlsx").Sheets(1).Range("A1").Clear
    Workbooks.Open("C:\Path\To\SomeClosedBook.xlsx")
    v = ActiveWorkbook.Sheets(1).Range("A1").Value
    Workbooks("SomeAlreadyOpenBook.xlsx").Activate
    ActiveWorkbook.Sheets("SomeSheet").Range("A1").Value = v
    Workbooks(2).Activate
    ActiveWorkbook.Close()
End Sub

And it would be better like:

Sub foo()
    Dim v as Variant
    Dim wb1 as Workbook
    Dim  wb2 as Workbook
    Set wb1 = Workbooks("SomeAlreadyOpenBook.xlsx")
    Set wb2 = Workbooks.Open("C:\Path\To\SomeClosedBook.xlsx")
    v = wb2.Sheets("SomeSheet").Range("A1").Value
    wb1.Sheets("SomeOtherSheet").Range("A1").Value = v
    wb2.Close()
End Sub

Pass ranges to your Subs and Functions as Range variables:

Sub ClearRange(r as Range)
    r.ClearContents
    '....
End Sub

Sub MyMacro()
    Dim rng as Range
    Set rng = ThisWorkbook.Worksheets("SomeSheet").Range("A1:B10")
    ClearRange rng
End Sub

You should also apply Methods (such as Find and Copy) to variables:

Dim rng1 As Range
Dim rng2 As Range
Set rng1 = ThisWorkbook.Worksheets("SomeSheet").Range("A1:A10")
Set rng2 = ThisWorkbook.Worksheets("SomeSheet").Range("B1:B10")
rng1.Copy rng2

If you are looping over a range of cells it is often better (faster) to copy the range values to a variant array first and loop over that:

Dim dat As Variant
Dim rng As Range
Dim i As Long

Set rng = ThisWorkbook.Worksheets("SomeSheet").Range("A1:A10000")
dat = rng.Value  ' dat is now array (1 to 10000, 1 to 1)
for i = LBound(dat, 1) to UBound(dat, 1)
    dat(i,1) = dat(i, 1) * 10 ' Or whatever operation you need to perform
next
rng.Value = dat ' put new values back on sheet

This is a small taster for what's possible.

Excel – VBA Copy Sheet to End of Workbook (with Hidden Worksheets)

Try this

Sub Sample()
    Dim test As Worksheet
    Sheets(1).Copy After:=Sheets(Sheets.Count)
    Set test = ActiveSheet
    test.Name = "copied sheet!"
End Sub

Looking back at this, a better approach would be

Set test = Sheets(Sheets.Count)

As correctly mentioned in the comments below, there are lot of things that needs to be considered when copying and renaming a sheet. Would recommend checking the other answers as well.

Best Answer

Related Solutions

Excel – How to avoid using Select in Excel VBA

Some examples of how to avoid select

Excel – VBA Copy Sheet to End of Workbook (with Hidden Worksheets)

Related Topic