If statement
I am trying to write a macro that renames worksheets. My problem is I don't
always have the same worksheets in each workbook. I would appreciate it if someone could give me some direction on how to write the if statement so that if the called out worksheet doesn't exist then it is skipped and the macro doesn't error out. Thank you in advance. Joe |
If statement
Joe,
See the macro below. HTH, Bernie MS Excel MVP Sub ProperWayToUseMulitpleErrorHandlers() Dim strName As String Check1: On Error GoTo ErrCheck1 strName = Worksheets("Sheet Doesn't Exist").Name MsgBox """Sheet Doesn't Exist"" exists and will be renamed" Worksheets("Sheet Doesn't Exist").Name = "New Name 1" 'Code to work with sheet "Sheet Doesn't Exist" Check2: On Error GoTo ErrCheck2 strName = Worksheets("Sheet Does Exist").Name MsgBox """Sheet Does Exist"" exists and will be renamed" Worksheets("Sheet Does Exist").Name = "New Name 2" 'Code to work with sheet "Sheet Does Exist" Check3: On Error GoTo ErrCheck3 strName = Worksheets("Sheet May Exist").Name MsgBox """Sheet May Exist"" exists and will be renamed" Worksheets("Sheet May Exist").Name = "New Name 3" 'Code to work with sheet "Sheet May Exist" Stopp: MsgBox "All Done" Exit Sub ErrCheck1: MsgBox """Sheet Doesn't Exist"" does not exist and cannot be renamed" Resume Check2 ErrCheck2: MsgBox """Sheet Does Exist"" does not exist and cannot be renamed" Resume Check3 ErrCheck3: MsgBox """Sheet May Exist"" does not exist and cannot be renamed" Resume Stopp End Sub "Joe" wrote in message ... I am trying to write a macro that renames worksheets. My problem is I don't always have the same worksheets in each workbook. I would appreciate it if someone could give me some direction on how to write the if statement so that if the called out worksheet doesn't exist then it is skipped and the macro doesn't error out. Thank you in advance. Joe |
If statement
Thank you very much for your assistance.
Joe "Bernie Deitrick" <deitbe @ consumer dot org wrote in message ... Joe, See the macro below. HTH, Bernie MS Excel MVP Sub ProperWayToUseMulitpleErrorHandlers() Dim strName As String Check1: On Error GoTo ErrCheck1 strName = Worksheets("Sheet Doesn't Exist").Name MsgBox """Sheet Doesn't Exist"" exists and will be renamed" Worksheets("Sheet Doesn't Exist").Name = "New Name 1" 'Code to work with sheet "Sheet Doesn't Exist" Check2: On Error GoTo ErrCheck2 strName = Worksheets("Sheet Does Exist").Name MsgBox """Sheet Does Exist"" exists and will be renamed" Worksheets("Sheet Does Exist").Name = "New Name 2" 'Code to work with sheet "Sheet Does Exist" Check3: On Error GoTo ErrCheck3 strName = Worksheets("Sheet May Exist").Name MsgBox """Sheet May Exist"" exists and will be renamed" Worksheets("Sheet May Exist").Name = "New Name 3" 'Code to work with sheet "Sheet May Exist" Stopp: MsgBox "All Done" Exit Sub ErrCheck1: MsgBox """Sheet Doesn't Exist"" does not exist and cannot be renamed" Resume Check2 ErrCheck2: MsgBox """Sheet Does Exist"" does not exist and cannot be renamed" Resume Check3 ErrCheck3: MsgBox """Sheet May Exist"" does not exist and cannot be renamed" Resume Stopp End Sub "Joe" wrote in message ... I am trying to write a macro that renames worksheets. My problem is I don't always have the same worksheets in each workbook. I would appreciate it if someone could give me some direction on how to write the if statement so that if the called out worksheet doesn't exist then it is skipped and the macro doesn't error out. Thank you in advance. Joe |
If statement
Sorry for the delay in my reply but this post worked beautifully. Thank you
for the help. Joe "Bernie Deitrick" <deitbe @ consumer dot org wrote in message ... Joe, See the macro below. HTH, Bernie MS Excel MVP Sub ProperWayToUseMulitpleErrorHandlers() Dim strName As String Check1: On Error GoTo ErrCheck1 strName = Worksheets("Sheet Doesn't Exist").Name MsgBox """Sheet Doesn't Exist"" exists and will be renamed" Worksheets("Sheet Doesn't Exist").Name = "New Name 1" 'Code to work with sheet "Sheet Doesn't Exist" Check2: On Error GoTo ErrCheck2 strName = Worksheets("Sheet Does Exist").Name MsgBox """Sheet Does Exist"" exists and will be renamed" Worksheets("Sheet Does Exist").Name = "New Name 2" 'Code to work with sheet "Sheet Does Exist" Check3: On Error GoTo ErrCheck3 strName = Worksheets("Sheet May Exist").Name MsgBox """Sheet May Exist"" exists and will be renamed" Worksheets("Sheet May Exist").Name = "New Name 3" 'Code to work with sheet "Sheet May Exist" Stopp: MsgBox "All Done" Exit Sub ErrCheck1: MsgBox """Sheet Doesn't Exist"" does not exist and cannot be renamed" Resume Check2 ErrCheck2: MsgBox """Sheet Does Exist"" does not exist and cannot be renamed" Resume Check3 ErrCheck3: MsgBox """Sheet May Exist"" does not exist and cannot be renamed" Resume Stopp End Sub "Joe" wrote in message ... I am trying to write a macro that renames worksheets. My problem is I don't always have the same worksheets in each workbook. I would appreciate it if someone could give me some direction on how to write the if statement so that if the called out worksheet doesn't exist then it is skipped and the macro doesn't error out. Thank you in advance. Joe |
All times are GMT +1. The time now is 05:22 PM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com