search for worksheet, insert new if doesn't exist
 

Im opening up a workbook and then selecting a worksheet based on
string variable like so (wsI is worksheet variable, sYear is searc
criteria)

Set wsI = Sheets(sYear)

I would like to improve this so that if the worksheet does not exist
insert a new one and then rename it to my search variable, i though
something along the lines of the below would work but it hasn't!
guess the 0 is the wrong thing to look for, any help appreciated

If Sheets(sYear) = 0 Then
Sheets.Add
ActiveSheet.Name = sYear
Else
Set wsI = Sheets(sYear)
End I

--
cereldin
-----------------------------------------------------------------------
cereldine's Profile: http://www.excelforum.com/member.php...fo&userid=3206
View this thread: http://www.excelforum.com/showthread.php?threadid=53500

search for worksheet, insert new if doesn't exist
 

Here is a function that tells you if a sheet exists in a given workbook

Public 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

If sheetexists(sYear) Then
Set wsI = Sheets(sYear)
Else
Sheets.Add
Set wsI = Activesheet
wsI.Name = sYear
End If

--
HTH...

Jim Thomlinson


"cereldine" wrote:


Im opening up a workbook and then selecting a worksheet based on a
string variable like so (wsI is worksheet variable, sYear is search
criteria)

Set wsI = Sheets(sYear)

I would like to improve this so that if the worksheet does not exist i
insert a new one and then rename it to my search variable, i thought
something along the lines of the below would work but it hasn't! I
guess the 0 is the wrong thing to look for, any help appreciated

If Sheets(sYear) = 0 Then
Sheets.Add
ActiveSheet.Name = sYear
Else
Set wsI = Sheets(sYear)
End If


--
cereldine
------------------------------------------------------------------------
cereldine's Profile: http://www.excelforum.com/member.php...o&userid=32069
View this thread: http://www.excelforum.com/showthread...hreadid=535008

search for worksheet, insert new if doesn't exist
 

On Error Resume Next
Set wsI = Worksheets(sYear)
On Error GoTo 0
If wsI Is Nothing Then
Worksheets.Add.Name = sYear
Set wsI = Worksheets(sYear)
End If

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

"cereldine" wrote
in message ...

Im opening up a workbook and then selecting a worksheet based on a
string variable like so (wsI is worksheet variable, sYear is search
criteria)

Set wsI = Sheets(sYear)

I would like to improve this so that if the worksheet does not exist i
insert a new one and then rename it to my search variable, i thought
something along the lines of the below would work but it hasn't! I
guess the 0 is the wrong thing to look for, any help appreciated

If Sheets(sYear) = 0 Then
Sheets.Add
ActiveSheet.Name = sYear
Else
Set wsI = Sheets(sYear)
End If


--
cereldine
------------------------------------------------------------------------
cereldine's Profile:
http://www.excelforum.com/member.php...o&userid=32069
View this thread: http://www.excelforum.com/showthread...hreadid=535008