Excel VBA check if a sheet already exists

Question

I have some code in Excel VBA, what it does is that whenever a user opens a specific workbook what it does it copies the content from the last sheet, then puts this new sheet at the end, and then it gives a new name to the sheet by putting a date format. The problem is that if for some reason you need to open again this workbook the same day it gives an error as the name of the sheet is already taken.

I'm trying to find a solution where if the name already exists then do not add a new sheet. Probably I've got some part of the code right but I'm guessing probably the order of the code lines is what is making it wrong.

Sub Auto()
Sheets(Sheets.Count).Select
Sheets(Sheets.Count).Copy After:=Sheets(Sheets.Count)
Sheets(Sheets.Count).Select
For i = 1 To Sheets.Count
    If Sheets(i).Name = UCase(Format(Now, "DDMMM")) Then
    exists = True
    End If

Next i
If Not exists Then
    Sheets(Sheets.Count).Name = UCase(Format(Now, "DDMMM"))
End If

Range("A3:P3").Select
ActiveCell.FormulaR1C1 = Format(Now, "medium date")

End Sub

I just added the loop condition but 2 things are wrong for this code. The if will go on and look up for every sheet in the workbook so if you have 200 sheets prob it will take sometime to go out of the loop. The other is that whenever you add a sheet that is a copy of another one Excel will add to the name of the sheet the corresponding copy (Sheet (2)) So the loop will do nothing as it will never find the same name.

So if you've got a better way to check if the sheet name already exists I will be glad to learn.

Thanks

Does this answer your question? [Test or check if sheet exists](https://stackoverflow.com/questions/6688131/test-or-check-if-sheet-exists) — Michael Wycisk, Jul 27 '20 at 22:13
I have read some of the suggestions there, what I can see is that the name is predefined for something. For name of a sheet what I am using is to format the name as a date. I have tried some of the solutions even the loop but the loop will go on and it won't find the same name because first you need the line code `Sheets(Sheets.Count).Name = UCase(Format(Now, "DDMMM"))` but if you try to run this before a loop it will give you a run error. — oma2484, Jul 27 '20 at 22:25
First loop and see if the sheet exists, or use the approach in the link provided in the first comment. If the worksheet exists, `Exit Sub`. Otherwise, do the copying and renaming. — BigBen, Jul 28 '20 at 00:57

score 0 · Accepted Answer · answered Jul 28 '20 at 00:58

0

You may like to use a function like the one below. You feed it a sheet name and it will return a sheet object by that name, either the one already existing or a new one just made.

Function GetSheet(SheetName As String) As Worksheet

    Dim Fun         As Worksheet            ' Function return object
    
    On Error Resume Next
    Set Fun = Worksheets(SheetName)
    ' if the sheet doesn't exist an error will occur here
    If Err Then
        Set Fun = Worksheets.Add(After:=Sheets(Sheets.Count))
        Fun.Name = SheetName
    End If
    Set GetSheet = Fun
End Function

You call that function from your Main procedure using syntax as shown below.

Sub Main()
    Dim Ws          As Worksheet
    Dim SheetName   As String
    
    ' Now() includes the time
    SheetName = UCase(Format(Date, "ddmmm"))
    Set Ws = GetSheet(SheetName)
    
    With Ws
        Debug.Print .Name
        .Cells(3, "A").Value = Format(Date, "medium date")
        .Range("A3:P3").Select
    End With
End Sub

Of course, when this code is integrated into your project the function is not likely to be on top. It would be at the bottom, under the main, or, if you have a lot of functions in your project, in a separate module dedicated to managing functions.

answered Jul 28 '20 at 00:58

Variatus

14,293
2
14
30

Yes! This is exactly what I was missing as I've didn't got the error directly with the if loop it didn't go and search for the name, but now with the function if the sheet has already a sheet with that name it will resume next. Although I'm still trying to figure out for what is the debug.print.name as I don't get to see any use for it right now. – oma2484 Jul 28 '20 at 01:32
Haha, there is no use for it. It's just proof that the function returned something, made visible in the Immediate window. Remove the line. BTW, When the sheet was created, it will be the ActiveSheet. If it already existed it probably isn't. Use `Ws.Activate` if you want that sheet to be active, or assign the ActiveSheet to an object variable at the start of the function and activate that sheet again at the end. Avoid selecting anything in the code. `.Range("A3:P3").Select` is for the user to see. Excel and VBA know it's there and you can access the range directly as shown in the line above it. – Variatus Jul 28 '20 at 02:23
Thanks, yes that line works fine the problem is `Set Fun = Worksheets.Add(After:=Sheets(Sheets.Count))` I want to Copy the content of the active sheet the same information. I tried change it for `Set Fun = Worksheets.Copy(After:=Sheets(Sheets.Count))` but now it tells me its expecting a function or a variable. I even tried to changed the class for sheets, but I just get the same error. I tried even the following code `If Err Then Sheets(Sheets.Count).Copy Set Fun = Worksheets.Add(After:=Sheets(Sheets.Count)) Fun.Name = SheetName End If Set GetSheet = Fun End Function` – oma2484 Jul 28 '20 at 13:38
Not clear. Do you want to create a copy of the ActiveSheet under the name like 29JUL? If so, what should happen if the sheet already exists? – Variatus Jul 29 '20 at 00:19

Excel VBA check if a sheet already exists

1 Answers1