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