If statement

[Joe]

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

 

[Bernie Deitrick]

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]

Thank you very much for your assistance.

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]

Sorry for the delay in my reply but this post worked beautifully. Thank you
for the help.

Joe