Re: $$Excel-Macros$$ Problem with Identify WorkSheet
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=wallref=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=wallref=ts
Re: $$Excel-Macros$$ Problem with Identify WorkSheet
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=wallref=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=wallref=ts
