The following simple macro will provide a quick and easy way to make sure a Worksheet exists in a Workbook before you start any changes or other processing on that worksheet.
This can be a problem when working with large numbers of worksheets that are created during a macro process. If you try to create a worksheet name that already exists your macro will fail with the following debug message
Now to avoid this error include the following code into your workbook and call it before you begin any work on an worksheet.
Check Worksheet Exists
Open the workbook that needs the Worksheet Exists check. Then start the Visual Basic Editor (Alt + F11) and copy the following macro into the editor
Public Function WorksheetExists(ByVal WorksheetName As String) As Boolean On Error Resume Next WorksheetExists = (Sheets(WorksheetName).Name <> "") On Error GoTo 0 End Function
Now wherever you need to check if a Worksheet exists simply include the following code to your workbook
If WorksheetExists(myWorkSheet) Then 'Your code goes here.... Else MsgBox "The worksheet does not exist" End If
If you have a question on this post for the Excel Help Desk team or have something you would like to share on this topic then please leave a comment.