Loading
Ozgrid Excel Help & Best Practices Forums

Excel Video Tutorials / Excel Dashboards Reports



Results 1 to 6 of 6

Thread: Create sheet if sheet doesn't exist

  1. #1
    Join Date
    4th April 2006
    Posts
    33

    Create sheet if sheet doesn't exist

    I need to be able to check if a certain worksheet exists in the active workbook. If that worksheet does not exist I need to make it. I already have the code for creating a new worksheet but I'm not sure how to check if the worksheet already exists. Can anyone give me a hand?
    Last edited by leep20; June 30th, 2006 at 05:31.

    Excel Video Tutorials / Excel Dashboards Reports


  2. #2
    Join Date
    8th September 2004
    Location
    Northampton, England
    Posts
    2,597

    Re: Create sheet if sheet doesn't exist

    Try setting an error trap, then assign a worksheet variable to the sheet you want to find and check whether it has been assigned, e.g.
    VB:
    Dim wsTest As Worksheet 
    Const strSheetName As String = "FindMe" 
     
    Set wsTest = Nothing 
    On Error Resume Next 
    Set wsTest = ActiveWorkbook.Worksheets(strSheetName) 
    On Error Goto 0 
     
    If wsTest Is Nothing Then 
        Worksheets.Add.Name = strSheetName 
    End If 
    
    
    Regards,
    Batman.

  3. #3
    Join Date
    8th September 2005
    Posts
    224

    Re: Create sheet if sheet doesn't exist

    Quote Originally Posted by Batman
    Try setting an error trap, then assign a worksheet variable to the sheet you want to find and check whether it has been assigned, e.g.
    VB:
    Dim wsTest As Worksheet 
    Const strSheetName As String = "FindMe" 
     
    Set wsTest = Nothing 
    On Error Resume Next 
    Set wsTest = ActiveWorkbook.Worksheets(strSheetName) 
    On Error Goto 0 
     
    If wsTest Is Nothing Then 
        Worksheets.Add.Name = strSheetName 
    End If 
    
    
    This is some clever and elagent code. I've modified it a bit so it's a function that receives the name of the sheet you want to add. If the function ends up creating the sheet, the function returns true, if not, it returns false.

    Thanx for the source code BatMan!!!


    VB:
    Function CreateSheetIf(strSheetName As String) As Boolean 
        Dim wsTest As Worksheet 
        CreateSheetIf = False 
         
        Set wsTest = Nothing 
        On Error Resume Next 
        Set wsTest = ActiveWorkbook.Worksheets(strSheetName) 
        On Error Goto 0 
         
        If wsTest Is Nothing Then 
            CreateSheetIf = True 
            Worksheets.Add.Name = strSheetName 
        End If 
         
    End Function 
     
     
    Sub Test() 
         'Create worksheet "Bob" if it doesn't exist.  Display Message box if
         'sheet is created.
        If CreateSheetIf("Bob") Then 
            MsgBox ("Welcome to the workbook Bob!") 
        End If 
         
         
         
    End Sub 
    
    
    Last edited by pdsasse; June 30th, 2006 at 06:21.

    Excel Video Tutorials / Excel Dashboards Reports


  4. #4
    Join Date
    4th April 2006
    Posts
    33

    Re: Create sheet if sheet doesn't exist

    Very creative! Thanks Batman and pdssase!
    Last edited by leep20; June 30th, 2006 at 07:47.

    Excel Video Tutorials / Excel Dashboards Reports


  5. #5
    Join Date
    8th September 2004
    Location
    Northampton, England
    Posts
    2,597

    Re: Create sheet if sheet doesn't exist

    Good idea, pdsasse. If I have a process that I use more than once in a program, or which takes quite a bit of coding, I almost always tend to put it into a separate sub-module or function with relevant parameters.

    If you wanted to put even more code into the separate process you could create it as a Sub-Module that checks for the existence and adds if required. That means only 1 line of code in your original program, e.g.
    VB:
    [LEFT][COLOR=blue]Sub[/COLOR] CreateSheet(ByVal strSheetName [COLOR=blue]As[/COLOR] [COLOR=blue]String[/COLOR]) 
    [COLOR=blue]Dim[/COLOR] wsTest [COLOR=blue]As[/COLOR] Worksheet 
     
    [COLOR=blue]Set[/COLOR] wsTest = [COLOR=blue]Nothing[/COLOR] 
    [COLOR=blue]On Error Resume Next[/COLOR] 
    [COLOR=blue]Set[/COLOR] wsTest = ActiveWorkbook.Worksheets(strSheetName) 
    [COLOR=blue]On Error Goto[/COLOR] 0 
     
    [COLOR=blue]If[/COLOR] wsTest [COLOR=blue]Is[/COLOR] [COLOR=blue]Nothing[/COLOR] [COLOR=blue]Then[/COLOR] 
    Worksheets.Add.Name = strSheetName 
    MsgBox "Sheet " & strSheetName & " created." 
    [COLOR=blue]End[/COLOR] [COLOR=blue]If[/COLOR] 
    [COLOR=blue]End Sub[/COLOR] 
     
    [COLOR=blue]Sub[/COLOR] Test() 
    [COLOR=darkgreen] 'Create worksheet "Bob" if it doesn't exist.[/COLOR]
    CreateSheet "Bob" 
    [COLOR=blue]End Sub[/COLOR] 
    [/LEFT] 
    
    

    It all depends on circumstances and preferences.
    Regards,
    Batman.

  6. #6
    Join Date
    16th June 2005
    Location
    Dublin
    Posts
    4,488

    Re: Create sheet if sheet doesn't exist

    This is the one I use, which to be fair, I got from OzGrid.... Thanks whoever it was.

    VB:
    Function AddSheetIfMissing(Name As String) As Worksheet 
         
        On Error Resume Next 
        Set AddSheetIfMissing = ThisWorkbook.Worksheets(Name) 
        If AddSheetIfMissing Is Nothing Then 
            Set AddSheetIfMissing = ThisWorkbook.Worksheets.Add 
            AddSheetIfMissing.Name = Name 
        End If 
         
    End Function 
    
    
    Ger
    _______________________________________________
    There are 10 types of people in the world. Those that understand Binary and those that dont.

    Why are Halloween and Christmas the same? Because Oct 31 = Dec 25...

    The BEST Lookup function of all time

    Dynamic Named Ranges are your bestest friend

    _______________________________________________

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Possible Answers

  1. Replies: 2
    Last Post: August 10th, 2008, 07:13
  2. Reference To A Sheet That Doesn't Yet Exist
    By Bryan021 in forum EXCEL HELP
    Replies: 2
    Last Post: February 22nd, 2007, 21:20
  3. If Exist Copy Row To Another Sheet
    By cozartistrue in forum EXCEL HELP
    Replies: 2
    Last Post: January 2nd, 2007, 20:01
  4. Replies: 1
    Last Post: July 19th, 2005, 03:34
  5. check if sheet exist
    By smhon in forum EXCEL HELP
    Replies: 2
    Last Post: April 24th, 2003, 19:15

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
porno