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

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

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

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