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" even though SheetName is passed and sometimes if function sheetExists function returns True still workbook is trying to add a worksheet
Aucun commentaire:
Enregistrer un commentaire