try this code Sub check() Dim i As Long Dim s, k As String s = InputBox("enter the sheet name to be added")
For i = 1 To Worksheets.Count k = Worksheets(i).Name If UCase(k) = UCase(s) Then MsgBox "Sheet Already Exists" Exit Sub End If Next i Sheets.Add After:=Sheets(Sheets.Count) Sheets(Sheets.Count).Name = s End Sub On Sun, Nov 14, 2010 at 2:17 PM, Krupesh Bhansali <krupeshbhans...@gmail.com > wrote: > Hi Friends > I have written following Macro to Add worksheet if it is not Exists in the > work book , but it is not working as it is not recognized Sheet is exits and > it always add one more work sheet. > > Please help > > Regards > krupesh > > > Option Explicit > Private Function SheetExist(Sname As String, Optional Wbname As String) As > Boolean > Dim WS As Worksheet > Dim Wb As Workbook > On Error Resume Next > If Len(Wbname) > 0 Then > Set Wb = Workbooks(Wbname) > If Wb Is Nothing Then Exit Function ' Exit if workbook is not open > Else > Set Wb = ActiveWorkbook > End If > Set WS = Wb.Sheet(Sname) > SheetExist = Not (WS Is Nothing) > End Function > > Sub Checkforsheet() > ShtExists = SheetExist("Sheet1") 'Calling Function from the above > If ShtExists Then > MsgBox " Worksheetis there" > Else > ActiveWorkbook.Sheets.Add > End If > End Sub > > -- > > ---------------------------------------------------------------------------------- > Some important links for excel users: > 1. Follow us on TWITTER for tips tricks and links : > http://twitter.com/exceldailytip > 2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310 > 3. Excel tutorials at http://www.excel-macros.blogspot.com > 4. Learn VBA Macros at http://www.quickvba.blogspot.com > 5. Excel Tips and Tricks at http://exceldailytip.blogspot.com > > To post to this group, send email to excel-macros@googlegroups.com > > <><><><><><><><><><><><><><><><><><><><><><> > Like our page on facebook , Just follow below link > http://www.facebook.com/pages/discussexcelcom/160307843985936?v=wall&ref=ts > -- *Regards* * * *Ashish Koul* *akoul*.*blogspot*.com <http://akoul.blogspot.com/> *akoul*.wordpress.com <http://akoul.wordpress.com/> My Linkedin Profile <http://in.linkedin.com/pub/ashish-koul/10/400/830> P Before printing, think about the environment. -- ---------------------------------------------------------------------------------- Some important links for excel users: 1. Follow us on TWITTER for tips tricks and links : http://twitter.com/exceldailytip 2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310 3. Excel tutorials at http://www.excel-macros.blogspot.com 4. Learn VBA Macros at http://www.quickvba.blogspot.com 5. Excel Tips and Tricks at http://exceldailytip.blogspot.com To post to this group, send email to excel-macros@googlegroups.com <><><><><><><><><><><><><><><><><><><><><><> Like our page on facebook , Just follow below link http://www.facebook.com/pages/discussexcelcom/160307843985936?v=wall&ref=ts