Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Delete values in sheet 2 that arre found in sheet 1 | Excel Discussion (Misc queries) | |||
If ID # on one sheet isn't found on another... | Excel Worksheet Functions | |||
how can I format sheet 1 if the value is found in sheet 2? | New Users to Excel | |||
Do same thing to each workbook found in a folder | Excel Programming | |||
Jump to next sheet if not found | Excel Programming |