if statement for sheet not found in workbook
 

dear all,
i want to add new worksheet if the worksheet name is not found in the
workbook when user input in the inputbox.

tosheet is the variable input by user.

** this if statement is incorrect. how should i write the statement?
If Worksheets(tosheet).Name not found in the workbook then

** this line is correct.
Set ws = ThisWorkbook.Worksheets.Add(after:=ThisWorkbook.Sh eets(ThisWorkbook.Sheets.Count))

End If


thanks alot

if statement for sheet not found in workbook
 

Sub test()
Dim ws As Worksheet, tosheet As String

tosheet = "this is a test"

With ThisWorkbook.Worksheets
On Error Resume Next
Set ws = .Item(tosheet)
On Error GoTo 0
If ws Is Nothing Then
Set ws = .Add(After:=.Item(.Count))
ws.Name = tosheet
End If
End With
End Sub


--
Rob van Gelder - http://www.vangelder.co.nz/excel


"tango" wrote in message
om...
dear all,
i want to add new worksheet if the worksheet name is not found in the
workbook when user input in the inputbox.

tosheet is the variable input by user.

** this if statement is incorrect. how should i write the statement?
If Worksheets(tosheet).Name not found in the workbook then

** this line is correct.
Set ws =
ThisWorkbook.Worksheets.Add(after:=ThisWorkbook.Sh eets(ThisWorkbook.Sheets.Count))

End If


thanks alot

if statement for sheet not found in workbook
 

One way:

Option Explicit
Sub testme()

Dim ToSheet As Worksheet
Dim ToSheetName As String

ToSheetName = InputBox(prompt:="what's the name")
If Trim(ToSheetName) = "" Then
Exit Sub '?
End If

Set ToSheet = Nothing
On Error Resume Next
Set ToSheet = Worksheets(ToSheetName)
On Error GoTo 0

If ToSheet Is Nothing Then
With ThisWorkbook
.Worksheets.Add after:=.Sheets(.Sheets.Count)
End With
Set ToSheet = ActiveSheet
On Error Resume Next
ToSheet.Name = ToSheetName
If Err.Number < 0 Then
MsgBox "Please rename " & ToSheet.Name & " manually!"
Err.Clear
End If
End If
End Sub


tango wrote:

dear all,
i want to add new worksheet if the worksheet name is not found in the
workbook when user input in the inputbox.

tosheet is the variable input by user.

** this if statement is incorrect. how should i write the statement?
If Worksheets(tosheet).Name not found in the workbook then

** this line is correct.
Set ws = ThisWorkbook.Worksheets.Add(after:=ThisWorkbook.Sh eets(ThisWorkbook.Sheets.Count))

End If

thanks alot

--

Dave Peterson

if statement for sheet not found in workbook
 

try

Sub AddSheetIF()
myname = InputBox("What name")
On Error Resume Next
If worksheets(myname) Is Nothing Then
worksheets.Add.Name = myname
End If
End Sub

--
Don Guillett
SalesAid Software

"tango" wrote in message
om...
dear all,
i want to add new worksheet if the worksheet name is not found in the
workbook when user input in the inputbox.

tosheet is the variable input by user.

** this if statement is incorrect. how should i write the statement?
If Worksheets(tosheet).Name not found in the workbook then

** this line is correct.
Set ws =
ThisWorkbook.Worksheets.Add(after:=ThisWorkbook.Sh eets(ThisWorkbook.Sheets.C
ount))

End If


thanks alot