Announcement

Collapse
No announcement yet.

Unconfigured Ad Widget

Collapse

Create sheet if sheet doesn't exist

Collapse
X
  • Filter
  • Time
  • Show
Clear All
new posts

  • 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, 05:31.

  • #2
    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.
    Code:
    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.

    Comment


    • #3
      Re: Create sheet if sheet doesn't exist

      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.
      Code:
      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!!!


      Code:
      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, 06:21.
      I hope this helps.
      Check out theseTemplates & Calculators!

      Comment


      • #4
        Re: Create sheet if sheet doesn't exist

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

        Comment


        • #5
          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.
          Code:
          Sub CreateSheet(ByVal strSheetName As String) Dim wsTest As Worksheet 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 MsgBox "Sheet " & strSheetName & " created." End If End Sub Sub Test() 'Create worksheet "Bob" if it doesn't exist. CreateSheet "Bob" End Sub

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

          Comment


          • #6
            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.

            Code:
            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

            Check out our new reputation system. Click on the "star" under the post!
            _______________________________________________

            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

            _______________________________________________

            Comment

            Trending

            Collapse

            There are no results that meet this criteria.

            Working...
            X