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