How do I check for existence of a worksheet?

L

LizzieHW

I'm trying to create a number of new worksheets based on the information in a
column on the original worksheet, and then copy the information from the
original sheet onto the relevent new sheet. I want to trap errors by first
checking if the required sheet exists before trying to create it, but I don't
know how to do this.
 
R

Ron de Bruin

Hi Lizzie

You can copy this function in a normal module

Function SheetExists(SName As String, _
Optional ByVal WB As Workbook) As Boolean
'Chip Pearson
On Error Resume Next
If WB Is Nothing Then Set WB = ThisWorkbook
SheetExists = CBool(Len(WB.Sheets(SName).Name))
End Function

And use this in your code in your loop

If SheetExists(cell.Value) = False Then
 
Top