CODEWITHSUNDEEP
vbaexcelif-statement
Ashwin
Ashwin

Reputation: 247

Avoid adding duplicate sheets to Excel Work book

I have a collection words inside SheetNames and i'm trying to add new Worksheet for each word inside SheetNames, please find the code below.

Before adding the Worksheet i'm trying to validate whether the sheet is already existing in my workbook using sheetExists function, code provided below.

For Each SheetName In SheetNames

     If sheetExists(SheetName , newWB) = False Then
        newWB.Activate
        Set FilPage = Worksheets.Add
        FilPage.Activate
        SheetName = Replace(Replace(Replace(Replace(Replace(SheetName, ".", " "), "[", " "), "]", " "), "/", "_"), "\", " ")
        If Len(SheetName) <= 30 Then
            FilPage.Name = SheetName
        Else
            SheetName = Left(SheetName, 23) & "-trimed"
        End If
        ActiveSheet.Range("A1").Activate
        ActiveCell.PasteSpecial
    End If
Next

The code valediction using function sheetExists is not working consistently.

Function sheetExists(sheetToFind ,wb As Excel.Workbook) As Boolean

    WS_Count = ActiveWorkbook.Worksheets.Count

    sheetExists = False

    For I = 1 To WS_Count
        If ActiveWorkbook.Worksheets(I).Name = sheetToFind Then
            sheetExists = True
            Exit Function
        End If
    Next

End Function

i can see some of the worksheet added with names "Sheet99" or "Sheet12" etc. even though SheetName is passed into the function. sometimes if sheetExists function returns True still workbook is trying to add a worksheet

Upvotes: 0

Views: 55

Answers (1)

A.S.H
A.S.H

Reputation: 29352

You have a parameter wb As Excel.Workbook to your Function sheetExists, great, use it! why use then the hazardous ActiveWorkbook, which has all chances to be some other WB than the one you want to examine?

Replace all occurrences of ActiveWorkbook with wb

Function sheetExists(sheetToFind ,wb As Excel.Workbook) As Boolean
    WS_Count = wb.Worksheets.Count  ' <-------------------------------------- wb
    sheetExists = False

    For I = 1 To WS_Count
        If wb.Worksheets(I).Name = sheetToFind Then    ' <------------------- wb
            sheetExists = True
            Exit Function
        End If
    Next
End Function

Moreover, the other code needs some corrections in addition to dropping the Activate stuff

For Each SheetName In SheetNames
     If Not sheetExists(SheetName , newWB) Then
        Set FilPage = newWB.Worksheets.Add
        SheetName = Replace(Replace(Replace(Replace(Replace(SheetName, ".", " "), "[", " "), "]", " "), "/", "_"), "\", " ")
        If Len(SheetName) > 30 Then SheetName = Left(SheetName, 23) & "-trimed"
        FilPage.Name = SheetName
        FilPage.Range("A1").PasteSpecial
    End If
Next

The function SheetExists can be further simplified as: (Copyright @DavidZemens)

Function sheetExists(sheetToFind,wb As Excel.Workbook) As Boolean
    Dim ws as Worksheet
    sheetExists = False
    On Error Resume Next
    Set ws = wb.Worksheets(sheetToFind)
    sheetExists = Not (ws Is Nothing)
End Function

Upvotes: 3

Related Questions