Find existing worksheet
 

Hi,

Hoping someone can help me.

I am creating a Macro in Excel 2000 and have run across a
problem. I have a file with numerous worksheets but
would like to search for one particular worksheet 'RECORD
TYPE THREE' in the file and if not found then add a new
worksheet with that name. I can add a new one, but do
not know how to search for an existing worksheet by that
name. Any help would be greatly appreciated.

Thanks,
Rob

Find existing worksheet
 

Hi Rob

One way

Sub test()
Dim N As Long
On Error Resume Next
With ThisWorkbook.Worksheets
N = Len(.Item("'RECORD TYPE THREE").Name)
If N = 0 Then
.Add(after:=.Item(.Count)).Name = "'RECORD TYPE THREE"
End If
End With
End Sub


--
Regards Ron de Bruin
(Win XP Pro SP-1 XL2000-2003)
www.rondebruin.nl



"Rob" wrote in message ...
Hi,

Hoping someone can help me.

I am creating a Macro in Excel 2000 and have run across a
problem. I have a file with numerous worksheets but
would like to search for one particular worksheet 'RECORD
TYPE THREE' in the file and if not found then add a new
worksheet with that name. I can add a new one, but do
not know how to search for an existing worksheet by that
name. Any help would be greatly appreciated.

Thanks,
Rob

Find existing worksheet
 

Here's an alternate method, though not necessarily better:

Sub AddSheetIfDoesntExist()

'Store the name of the worksheet for multiple use
Const szNEW_SHEET As String = "RECORD TYPE THREE"

'Use thsi variable to check for the worksheet's existence
Dim wks As Worksheet

'Suppress VBA/Macro Error handling
On Error Resume Next
'Try to store
Set wks = ActiveWorkbook.Worksheets(szNEW_SHEET)
'From this line onward, when an error occurs jump to the "Error:" line
On Error GoTo Error

'If the variable has nothing in it then the worksheet doesn't exist
If wks Is Nothing Then
'Add the worksheet
ActiveWorkbook.Worksheets.Add
'Rename the new worksheet (which when added becomes the active
sheet)
ActiveSheet.Name = szNEW_SHEET
End If

'Stop the macro here. The code below it only runs when an error exists
Exit Sub
Error:
'Display the error number and description in a message box
MsgBox Err.Number & vbLf & vbLf & Err.Description, vbCritical
End Sub


--
__________________
Robert Rosenberg
R-COR Consulting Services
Microsoft MVP - Excel


"Rob" wrote in message
...
Hi,

Hoping someone can help me.

I am creating a Macro in Excel 2000 and have run across a
problem. I have a file with numerous worksheets but
would like to search for one particular worksheet 'RECORD
TYPE THREE' in the file and if not found then add a new
worksheet with that name. I can add a new one, but do
not know how to search for an existing worksheet by that
name. Any help would be greatly appreciated.

Thanks,
Rob

Find existing worksheet
 

The following FOR statement loops through each worksheet
in the active workbook, add your code below to check the
worksheet name and if the FOR loop has finished, add a new
worksheet named (X).

For Each Worksheet In Worksheets

This is the only way i know of checking if a particular
worksheet name exists.

-----Original Message-----
Hi,

Hoping someone can help me.

I am creating a Macro in Excel 2000 and have run across a
problem. I have a file with numerous worksheets but
would like to search for one particular worksheet 'RECORD
TYPE THREE' in the file and if not found then add a new
worksheet with that name. I can add a new one, but do
not know how to search for an existing worksheet by that
name. Any help would be greatly appreciated.

Thanks,
Rob
.