Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Check Activesheet for chart sheet or work sheet | Charts and Charting in Excel | |||
Enable check box in protected sheet + group check boxes | Excel Discussion (Misc queries) | |||
how to use sumif function to check date in 1 sheet is < 2 sheet | Excel Worksheet Functions | |||
error handling - check chart existance | Excel Programming | |||
Existance Check Fails | Excel Programming |