CODEWITHSUNDEEP
vbaexcelerror-handlingexists
user2457541
user2457541

Reputation: 105

If sheet doesn't exist, create it (VBA Excel)

I am importing data from Microsoft Access, the error detection doesn't seem to be working. If the code tries to create a sheet that already exists, keep going and set the destinationsheet to the already present sheet. Any tips wold be appreciated.

For ix = stWW To edWW
    For modi = 0 To mdcnt - 1

  On Error Resume Next
    Sheets.Add(After:=Sheets(Sheets.Count)).Name = ix & " " & modvar(modi)
  On Error GoTo 0

    Set DestinationSheet = Worksheets(ix & " " & modvar(modi))


     'strSQL2 = "SELECT 1302_Scan.* FROM 1302_Scan;"
     strSQL = "SELECT " & ix & "_" & modvar(modi) & ".* FROM " & ix & "_" & modvar(modi) & ";"

    'MsgBox strSQL & "|" & strSQL

    DestinationSheet.Cells.Clear

used earlier in code, may be causing error to @sous2817's suggestion.

On Error GoTo continue
Do While Not IsNull(modvar(ii))
    mdcnt = mdcnt + 1
    ii = ii + 1

Loop
continue:
On Error GoTo 0

Upvotes: 3

Views: 20060

Answers (4)

JCA
JCA

Reputation: 21

I had a similar problem and solved it this way

    On Error GoTo Catch 
Try:
    Sheets("name_of_tab").Select 'Try to focus on the tab
    GoTo Finally
Catch:
    Sheets.Add(After:=Sheets(Sheets.Count)).Name = "name_of_tab" 'If it fails create it
Finally:
    On Error GoTo 0

Upvotes: 2

Nicholas Stom
Nicholas Stom

Reputation: 330

This should work. Good Luck.

Function Add_Sheet(sheet_name As String)
    Dim i, sheet_exists As Integer
    sheet_exists = 0
    For i = 1 To Sheets.Count
       If Sheets(i).Visible = -1 Then
           If Sheets(i).Name = sheet_name Then
                sheet_exists = 1
           End If
       End If
    Next
    If sheet_exists = 0 Then
        Sheets.Add(After:=Sheets(Sheets.Count)).Name = sheet_name
    End If
End Function

Upvotes: 0

Amelia B
Amelia B

Reputation: 1084

This might be an old question and there are plenty of good and working answer to it(all around the internet). But I just stumbled upon it myself and no answer I found directly satisfied my programming style. In the end I ended up re-creating a try...catch...finally structure and I think it looks very clean. So for people stumbling upon this problem and searching for a generalzied and easy to adopt solution:

    Dim sheetToCreate As Worksheet
    Dim sheetToCreateName As String: sheetToCreateName = "Name"

    On Error GoTo Catch
Try:
    Set sheetToCreate = wb.Worksheets(sheetToCreateName)
    GoTo Finally
Catch:
    Set sheetToCreate = wb.Worksheets.Add
    sheetToCreate.Name = sheetToCreateName 
Finally:
    On Error GoTo 0

Upvotes: 0

sous2817
sous2817

Reputation: 3960

This may be a time when controlled use of On Error Resume Next is acceptable. Maybe something like:

On Error Resume Next
Sheets.Add(After:=Sheets(Sheets.Count)).Name = ix & " " & modvar(modi)
On Error GoTo 0

this assumes that ix & " " & modvar(modi) resolves to a proper name.

A bit of context to my answer:

At the core, you don't really care if the sheet exists or not, you just care that it exists. Creating a sheet with a specific sheet name will generate an error and not create the new sheet (because of the error). So wrapping the creation in an "on error resume next" will create the sheet if it doesn't exist and skip the error if it already exists. The next line (on error goto 0) turns the error checking back on so that you can appropriately handle errors that may occur later in your code.

Upvotes: 5

Related Questions