Worksheets.Add --- IF it doesn't already exist
 

I'm looping through some workbooks in a directory, then adding new
sheets to my active workbook. How can I test to make sure a worksheet
doesnt exist before I add it? And if it does exist how do I activate
that sheet? Something like this maybe?


If ThisWorkbook.Worksheets < Left$(bk.Name, 3) Then
Set sh2 = ThisWorkbook.Worksheets.Add
sh2.Name = Left$(bk.Name, 3)

' Do stuff with sh2

Else
Set sh2 = Left$(bk.Name, 3)

' Do stuff with sh2

End If


Thanks,
-- Dan

Worksheets.Add --- IF it doesn't already exist
 

Somthing like this perhaps

dim wks as worksheet

on error resume next
set wks = sheets("This")
on error goto 0

if wks is nothing then
set wks = thisworkbook.worksheets.add
wks.name = "This"
end if
thisworkbook.activate
wks.select
--
HTH...

Jim Thomlinson


"Dan R." wrote:

I'm looping through some workbooks in a directory, then adding new
sheets to my active workbook. How can I test to make sure a worksheet
doesnt exist before I add it? And if it does exist how do I activate
that sheet? Something like this maybe?


If ThisWorkbook.Worksheets < Left$(bk.Name, 3) Then
Set sh2 = ThisWorkbook.Worksheets.Add
sh2.Name = Left$(bk.Name, 3)

' Do stuff with sh2

Else
Set sh2 = Left$(bk.Name, 3)

' Do stuff with sh2

End If


Thanks,
-- Dan

Worksheets.Add --- IF it doesn't already exist
 

I stole this from Chip Pearson:

Function WorksheetExists(SheetName As Variant, _
Optional WhichBook As Workbook) As Boolean
'from Chip Pearson
Dim WB As Workbook
Set WB = IIf(WhichBook Is Nothing, ThisWorkbook, WhichBook)
On Error Resume Next
WorksheetExists = CBool(Len(WB.Worksheets(SheetName).Name) 0)
End Function

'and you can use it like:
....
if worksheetexists("myname",activeworkbook) then

=========

So in your case...

if worksheetexists(left(bk.name,3), thisworkbook) then
'it exists
application.goto thisworkbook.worksheets(left(bk.name,3)).range("a1 ")
else
'add it.
end if

Or it would seem more natural to:

if worksheetexists(left(bk.name,3), thisworkbook) then
'do nothing
else
'add it.
end if
'always go to it.
application.goto thisworkbook.worksheets(left(bk.name,3)).range("a1 ")


"Dan R." wrote:

I'm looping through some workbooks in a directory, then adding new
sheets to my active workbook. How can I test to make sure a worksheet
doesnt exist before I add it? And if it does exist how do I activate
that sheet? Something like this maybe?

If ThisWorkbook.Worksheets < Left$(bk.Name, 3) Then
Set sh2 = ThisWorkbook.Worksheets.Add
sh2.Name = Left$(bk.Name, 3)

' Do stuff with sh2

Else
Set sh2 = Left$(bk.Name, 3)

' Do stuff with sh2

End If

Thanks,
-- Dan

--

Dave Peterson

Worksheets.Add --- IF it doesn't already exist
 

That's perfect Dave.

Thanks guys,
-- Dan