Does anyone know the code for checking if a worksheet exists. The worksheets name will be the String "NewSht" followed by either the letter "L" or "B". I'm trying to check if the sheet exists, and if it does, overwrite all its cells. If it doesn't, I need to create a new sheet with that name. Thanks for the help.
-
If you would like to post, please check out the MrExcel Message Board FAQ and register here. If you forgot your password, you can reset your password.
VBA to check if a worksheet exists
- Thread starter jrnyman
- Start date
Dave Hawley
Rest in Peace
- Joined
- Feb 28, 2002
- Messages
- 1,582
Hi
If you are going to overwrite them anyway, Just use this:
Application.DisplayAlerts = False
On Error Resume Next
Sheets("NewShtL").Delete
Sheets("NewShtB").Delete
Application.DisplayAlerts = True
On Error GoTo 0
Sheets.Add().Name = "NewSht"
But to check if sheet exists you would use
Dim wsSheet As Worksheet
On Error Resume Next
Set wsSheet = Sheets("NewShtL")
On Error GoTo 0
If Not wsSheet Is Nothing Then
MsgBox "I do exist"
Else
MsgBox "I do NOT exist"
End If
If you are going to overwrite them anyway, Just use this:
Application.DisplayAlerts = False
On Error Resume Next
Sheets("NewShtL").Delete
Sheets("NewShtB").Delete
Application.DisplayAlerts = True
On Error GoTo 0
Sheets.Add().Name = "NewSht"
But to check if sheet exists you would use
Dim wsSheet As Worksheet
On Error Resume Next
Set wsSheet = Sheets("NewShtL")
On Error GoTo 0
If Not wsSheet Is Nothing Then
MsgBox "I do exist"
Else
MsgBox "I do NOT exist"
End If
Upvote
0
Colo
MrExcel MVP,
- Joined
- Mar 20, 2002
- Messages
- 1,659
- Office Version
- 2016
- 2003 or older
- Platform
- Windows
Hi. The following code is using Loop.
Please try. Regards,
Sub Test()
Dim sh As Worksheet, flg As Boolean
For Each sh In Worksheets
If sh.Name Like "NewSht*" Then flg = True: Exit For
Next
If flg = True Then
MsgBox "Found!"
Else
Sheets.Add.Name = "NewSht"
End If
End Sub
Please try. Regards,
Sub Test()
Dim sh As Worksheet, flg As Boolean
For Each sh In Worksheets
If sh.Name Like "NewSht*" Then flg = True: Exit For
Next
If flg = True Then
MsgBox "Found!"
Else
Sheets.Add.Name = "NewSht"
End If
End Sub
Upvote
0
Spencer Jobe
New Member
- Joined
- Jun 16, 2009
- Messages
- 4
Public Function WorksheetExists(ByVal WorksheetName As String) As Boolean
On Error Resume Next
WorksheetExists = (Sheets(WorksheetName).Name <> "")
On Error GoTo 0
End Function
On Error Resume Next
WorksheetExists = (Sheets(WorksheetName).Name <> "")
On Error GoTo 0
End Function
Last edited:
Upvote
0
High Plains Grifter
Board Regular
- Joined
- Mar 9, 2010
- Messages
- 129
I am having problems implementing the code to check whether a sheet already exists in my own macro - for some reason, the following code always seems to think that the new sheet name already exists, and asks whether I would like to overwrite it, even when the sheet in question patently does not exist. Below is a section of the macro, containing the relevant code:
I cannot work out which part I have got wrong - can anyone help?
Thanks for looking at the post, and all replies are much appreciated!
Code:
Sub NewSheets()
Dim agent As String
Dim lastone As String
Dim i As Integer
Dim max As Integer
Dim ws As Worksheet
Application.ScreenUpdating = False
Application.DisplayAlerts = False
max = InputBox("How Many Rows down the list do you wish to use?", "Enter a number to create that number of sheets")
For i = 1 To max
'for the first of the new sheets, the previous sheet is called template, by definition
If i = 1 Then
lastone = "Template"
'otherwise, the last previous sheet name will be one higher in the list than the current
Else
lastone = Worksheets("Lists").Range("A1").Offset(i - 1, 0).Value
End If
'get the agent name
agent = Worksheets("Lists").Range("A1").Offset(i, 0).Value
'check whether a sheet with that name already exists
On Error Resume Next
Set ws = Sheets(agent)
'if none with that name, create a new sheet
If ws Is Nothing Then
ActiveWorkbook.Sheets("Template").Copy after:=ActiveWorkbook.Sheets(lastone)
ActiveSheet.Name = agent
'if the sheet already exists, ask whether the user wants to replace it
Else
overW = MsgBox("This sheet already exists - Overwrite?", vbYesNoCancel, agent)
If overW = vbYes Then
Sheets(agent).Delete
ActiveWorkbook.Sheets("Template").Copy after:=ActiveWorkbook.Sheets(lastone)
ActiveSheet.Name = agent
ElseIf overW = vbNo Then
GoTo Skip
ElseIf overW = vbCancel Then
Exit Sub
End If
End IfI cannot work out which part I have got wrong - can anyone help?
Thanks for looking at the post, and all replies are much appreciated!
Upvote
0
xenou
MrExcel MVP
- Joined
- Mar 2, 2007
- Messages
- 16,836
- Office Version
- 2019
- Platform
- Windows
Possibly you have not reset ws as nothing:
Try amending to:
Also not that error handling will be left "on" and may mask other problems now. So also preferable is:
Code:
On Error Resume Next
Set ws = Sheets(agent)Try amending to:
Code:
[COLOR="Blue"]Set ws = Nothing[/COLOR]
On Error Resume Next
Set ws = Sheets(agent)Also not that error handling will be left "on" and may mask other problems now. So also preferable is:
Code:
Set ws = Nothing
On Error Resume Next
Set ws = Sheets(agent)
[COLOR="Blue"]On Error GoTo 0[/COLOR]
Upvote
0
High Plains Grifter
Board Regular
- Joined
- Mar 9, 2010
- Messages
- 129
Thanks
Thanks - worked perfectly. I will look up what "on error goto 0" actually does and thus try to avoid that in the future.
Thanks - worked perfectly. I will look up what "on error goto 0" actually does and thus try to avoid that in the future.
Upvote
0
Lil Smiles
New Member
- Joined
- Jan 31, 2012
- Messages
- 1
Hey Guys,
I saw this thread and I wanted to show the code I came up with.
I don't like "On Error Resume Next" command so I made the following function:
I saw this thread and I wanted to show the code I came up with.
I don't like "On Error Resume Next" command so I made the following function:
Code:
Sub Testing()
Dim SheetName1, SheetName2 As String
Dim Result As Boolean
Dim i As Long
SheetName = Array("laskgfasdfalskg", "Config")
For i = 0 To UBound(SheetName)
Result = WorksheetExists(SheetName(i))
If Result = False Then
MsgBox "Sheet name " & SheetName(i) & " doesn't exist!"
Else
MsgBox "Sheet name " & SheetName(i) & " does exist!"
End If
Next i
End Sub
Public Function WorksheetExists(ByVal WorksheetName As String) As Boolean
Dim Sht As Worksheet
WorksheetExists = False
For Each Sht In ActiveWorkbook.Worksheets
If Sht.Name = WorksheetName Then worksheetexits = True
Next Sht
End Function
Upvote
0
Here is a working version
Function WorksheetExists(ByVal WorksheetName As String) As Boolean
Dim Sht As Worksheet
For Each Sht In ThisWorkbook.Worksheets
If Application.Proper(Sht.Name) = Application.Proper(WorksheetName) Then
WorksheetExists = True
Exit Function
End If
Next Sht
WorksheetExists = False
End Function
Function WorksheetExists(ByVal WorksheetName As String) As Boolean
Dim Sht As Worksheet
For Each Sht In ThisWorkbook.Worksheets
If Application.Proper(Sht.Name) = Application.Proper(WorksheetName) Then
WorksheetExists = True
Exit Function
End If
Next Sht
WorksheetExists = False
End Function
Upvote
0
Similar threads
- Question
