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

Question

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

sous2817 · Accepted Answer · 2013-06-20 16:49:37Z

5

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.

edited Jun 20, 2013 at 16:49

answered Jun 20, 2013 at 16:39

sous2817

3,9602 gold badges37 silver badges37 bronze badges

Sign up to request clarification or add additional context in comments.

10 Comments

user2457541 Over a year ago

Thanks a lot, my problem is almost solve. The last issue I am having is if the sheet already exists, it say "Cannot rename a sheet to the same name as another sheet, a referenced object library or workbook referenced by visual basic. I did use a error goto (added an edit to code above), but I put the error goto afterwards so I thought it wouldn't cause a problem?

sous2817 Over a year ago

@user2457541 The logic is probably the same right? Rename a sheet to something and if that sheet name already exists, skip that part and keep going. Is that a correct assumption? If so, just add another on error resume next right above the line that renames your code and on error goto 0 right after the line. If that's not correct, you'll have to edit your post and I'll take another stab at it.

user2457541 Over a year ago

I have edited my question. What you are saying makes complete sense, I tried doing a similar thing basically with an iserror(ws.name). But the code is executing as if the on error resume next isn't there.

sous2817 Over a year ago

@user2457541 I don't think iserror(ws.name) is the same as what I'm suggesting. Can you edit your post to include the line where you're renaming sheets? I don't see that in what you have posted above.

user2457541 Over a year ago

Sheets.Add(After:=Sheets(Sheets.Count)).Name = ix & " " & modvar(modi) is the line where it says I am renaming. In the workbook it creates a sheet* then tries to name it ix & " " & modvar(modi).

|

JCA · Accepted Answer · 2021-02-11 10:38:08Z

2

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

answered Feb 11, 2021 at 10:38

JCA

213 bronze badges

1 Comment

Veki Over a year ago

This should be the selected answer. This works for all cases.

Amelia B · Accepted Answer · 2018-02-23 07:33:51Z

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

Nicholas Stom · Accepted Answer · 2019-10-15 23:02:06Z

0

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

answered Oct 15, 2019 at 23:02

Nicholas Stom

3301 gold badge3 silver badges12 bronze badges

Collectives™ on Stack Overflow

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

4 Answers 4

10 Comments

1 Comment

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

10 Comments

1 Comment

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related