Link to home
Start Free TrialLog in
Avatar of Flora Edwards
Flora EdwardsFlag for Sweden

asked on

help with changing the VBA to ignore if sheet exists

I have this code that adds sheets.

i need help with changing it,  that if sheet already exists then do not do anything.

Option Explicit                        'Turn on compiler option requiring
                                       'that all variables be declared
Sub NewSheets()

  Dim shArray() As Variant             'Declare the sheet Name array and a
  Dim i As Long                        'counter variable

  shArray = Array("CustomerTable", _
                  "EmployeeTable", _
                  "OrdersTable", _
                  "ProductTable", _
                  "PriceAdjustment")   'Populate the array

  For i = LBound(shArray) To UBound(shArray)  'Loop through the elements
      Sheets.Add().Name = shArray(i)
  Next i

End Sub

Open in new window

SOLUTION
Avatar of Ryan Chong
Ryan Chong
Flag of Singapore image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of Flora Edwards

ASKER

thanks.

how can i incorporate the following UDF into the code above, so that if SheetExist is not true then create otherwise do nothing.

Function SheetExists(shtName As String, Optional wb As Workbook) As Boolean

    Dim sht                   As Worksheet

    If wb Is Nothing Then Set wb = ActiveWorkbook
    On Error Resume Next
    Set sht = wb.Sheets(shtName)
    On Error GoTo 0
    SheetExists = Not sht Is Nothing

End Function

Open in new window

SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
ASKER CERTIFIED SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
thanks very much
You're welcome and I'm glad I was able to help.

If you expand the “Full Biography” section of my profile you'll find links to some articles I've written that may interest you.

Marty - Microsoft MVP 2009 to 2016
              Experts Exchange MVE 2015
              Experts Exchange Top Expert Visual Basic Classic 2012 to 2016