How to check for the existance of a Sheet (or not)
 

Hi to all

I have writen a routine to analyse an e-telephone account
The idea is that it should create a new sheet (if it doesn't exist) for each
different telephone number it comes accross.
I use an error-check for the existance of the sheet. If an error occurs
(i.e. fails the 'Select' sheet line it then goes to the lable 'NoSuchSheet'
where it copies a template and then re-names it.

All works well if only one new telephone sheet needs to be created. ie On
Error works only once but 'bombs' the second time.

I am looking for an alternative way of checking for the existance of a sheet
without incurrring an error.

This snipit works if only one sheet is not in existance
For Each TelNo In rngTelNos
On Error GoTo 0
If TelNo < "" Then
SheetTelNoName = "Acc_" & TelNo
On Error GoTo NoSuchSheet
Sheets(SheetTelNoName).Select
On Error GoTo 0
GoTo FoundSheet
NoSuchSheet:
On Error GoTo 0
Sheets("Acc Template").Visible = True
Sheets("Acc Template").Select
Sheets("Acc Template").Copy After:=Sheets(2)
Sheets("Acc Template").Visible = False
Sheets("Acc Template (2)").Select
Sheets("Acc Template (2)").Name = SheetTelNoName
Sheets("All Accounts").Select
FoundSheet:
End If
On Error GoTo 0
Next TelNo

How to check for the existance of a Sheet (or not)
 

Once an error has been raised, VBA is executing in "error mode",
and On Error statements will have no effect. You need to restore
VBA to "normal mode" with a Resume statement.


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com




"Pete" wrote in message
...
Hi to all

I have writen a routine to analyse an e-telephone account
The idea is that it should create a new sheet (if it doesn't
exist) for each
different telephone number it comes accross.
I use an error-check for the existance of the sheet. If an
error occurs
(i.e. fails the 'Select' sheet line it then goes to the lable
'NoSuchSheet'
where it copies a template and then re-names it.

All works well if only one new telephone sheet needs to be
created. ie On
Error works only once but 'bombs' the second time.

I am looking for an alternative way of checking for the
existance of a sheet
without incurrring an error.

This snipit works if only one sheet is not in existance
For Each TelNo In rngTelNos
On Error GoTo 0
If TelNo < "" Then
SheetTelNoName = "Acc_" & TelNo
On Error GoTo NoSuchSheet
Sheets(SheetTelNoName).Select
On Error GoTo 0
GoTo FoundSheet
NoSuchSheet:
On Error GoTo 0
Sheets("Acc Template").Visible = True
Sheets("Acc Template").Select
Sheets("Acc Template").Copy After:=Sheets(2)
Sheets("Acc Template").Visible = False
Sheets("Acc Template (2)").Select
Sheets("Acc Template (2)").Name = SheetTelNoName
Sheets("All Accounts").Select
FoundSheet:
End If
On Error GoTo 0
Next TelNo

How to check for the existance of a Sheet (or not)
 

Hi Pete

You can use a function in your module

Function SheetExists(SName As String, _
Optional ByVal wb As Workbook) As Boolean
'Chip Pearson
On Error Resume Next
If wb Is Nothing Then Set wb = ThisWorkbook
SheetExists = CBool(Len(wb.Sheets(SName).Name))
End Function

And use this in your macro

If SheetExists("test") = False Then
'.....
End If



--
Regards Ron de Bruin
http://www.rondebruin.nl



"Pete" wrote in message ...
Hi to all

I have writen a routine to analyse an e-telephone account
The idea is that it should create a new sheet (if it doesn't exist) for each
different telephone number it comes accross.
I use an error-check for the existance of the sheet. If an error occurs
(i.e. fails the 'Select' sheet line it then goes to the lable 'NoSuchSheet'
where it copies a template and then re-names it.

All works well if only one new telephone sheet needs to be created. ie On
Error works only once but 'bombs' the second time.

I am looking for an alternative way of checking for the existance of a sheet
without incurrring an error.

This snipit works if only one sheet is not in existance
For Each TelNo In rngTelNos
On Error GoTo 0
If TelNo < "" Then
SheetTelNoName = "Acc_" & TelNo
On Error GoTo NoSuchSheet
Sheets(SheetTelNoName).Select
On Error GoTo 0
GoTo FoundSheet
NoSuchSheet:
On Error GoTo 0
Sheets("Acc Template").Visible = True
Sheets("Acc Template").Select
Sheets("Acc Template").Copy After:=Sheets(2)
Sheets("Acc Template").Visible = False
Sheets("Acc Template (2)").Select
Sheets("Acc Template (2)").Name = SheetTelNoName
Sheets("All Accounts").Select
FoundSheet:
End If
On Error GoTo 0
Next TelNo