Scossa pretended :
I apologize in advance for my bad english.
Ok, but if O.P. run your code or run my code, he can not understand
based on the results obtained if runned your code or my code.
If the sheet already existed then it would be deleted. The OP wanted to
know how to detect if the sheet exists. I merely presented one way.
Others have shown alternative methods. I'm afraid I don't get what's
not to understand!
So ... why loops across all sheets if code not advise user that
founded sheet?
As mentioned, this is just one way to test if a sheet exists. There are
better (more efficient) ways as others have shown. The OP did not ask
for a means to notify the user if the sheet did already exist.
<IMO>
If the sheet already existed I would clear its contents and use it. If
it didn't exist then I would add it. The code require to do it this way
would/could be more efficient, and better self-documenting. No need to
use error handling for that purpose since it can be done within a
reusable function like this:
Function bSheetExists(WksName As String) As Boolean
' Checks if a specified worksheet exists.
' Arguments: WksName The name of the worksheet
' Returns: TRUE if the sheet exists
Dim x As Worksheet
On Error Resume Next
Set x = ActiveWorkbook.Sheets(WksName)
bSheetExists = (Err = 0)
End Function
OR this one-liner:
Function WorksheetExists(WSName As String, Optional wb As
Excel.Workbook = Nothing) As Boolean '//by Chip Pearson
On Error Resume Next
WorksheetExists = CBool(Len(IIf(wb Is Nothing, ThisWorkbook,
wb).Worksheets(WSName).name))
End Function
And as for an example of using either of these:
If bSheetExists("Test") Then
'If WorksheetExists("Test", ActiveWorkbook) Then 'optional method
Set wksTarget = Sheets("Test")
wksTarget.UsedRange.ClearContents
Else
Set wksTarget = ActiveWorkbook.Sheets.Add
wksTarget.Name = "Test"
End If
...just for clarity!
