Hi Krupesh, I have written below code for you. Its working fine, you can customize it as per your need. Hope this will help you.
Sub Find_Sheet() Dim sh As Worksheet Dim sTemp As String sTemp = InputBox("Please enter the sheet name to find:") For Each sh In ActiveWorkbook.Sheets If sh.Name = sTemp Then MsgBox "Sheet already exist" MsgBox sh.Name Exit Sub End If Next sh MsgBox "Sheet doesn't exist" ActiveWorkbook.Sheets.Add ActiveSheet.Name = sTemp End Sub Regards, Deepak Rai 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, Deepak Rai -- ---------------------------------------------------------------------------------- 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