worksheet if...then

[excelmacro]

I need to create a macro that will search my workbook for sheet "Data" and if there is no sheet called "Data" then create a new sheet and name it Data. If Data is present then the macro does nothing. Any suggestions?

 

[Jay Petrulis]

The routine Add_Data_Sheet() calls a private function SheetExists, so both are required.

Sub Add_Data_Sheet()

If SheetExists("Data") = False Then
    Worksheets.Add After:=Sheets(ThisWorkbook.Worksheets.Count)
    ActiveSheet.Name = "Data"
End If

End Sub

Private Function SheetExists(sheetname) As Boolean
Dim abc As Object
On Error Resume Next
Set abc = ActiveWorkbook.Sheets(sheetname)
If Err = 0 Then SheetExists = True _
Else SheetExists = False
End Function

 

[excelmacro]

i get a compile error: else without if with

Else: SheetExists = False

 

[Jay Petrulis]

I cannot replicate your error.

Try to rewrite it as:

If Err = 0 Then
    SheetExists = True
Else
    SheetExists = False
End If

 

[Jay Petrulis]

I *can* replicate it.

You need to insert an underscore at the end of the first line, as this is actually only one line.

If Err = 0 Then SheetExists = True Else SheetExists = False
This message was edited by Jay Petrulis on 2002-05-03 14:29

 

[excelmacro]

both those worked. thanks