Error Handling Question

[ARobb4]

Greetings,

My situation is this, i want to have different error messages for an error that has the same error number. pls help

thanks

Sub CopyTemp()

Dim WWcellname As String
Dim Pcellname As String
Dim Tcellname As String
Dim Workshets As String
Dim Workshets1 As String
WWcellname = Range("B1").Value
Pcellname = Range("B2").Value
Tcellname = Range("B3").Value

On Error GoTo err1
Sheets(Tcellname).Select
Sheets(Tcellname).Copy Before:=Sheets(Pcellname)
Sheets(Tcellname & " (2)").Name = WWcellname

err1:
If WWcellname = "" Then
MsgBox (Space(55) & "WARNING!!!" & vbNewLine & "The Work Week name you have entered is blank. Therefore, the work week you are trying to create will be named by its default name.")

ElseIf Pcellname = "" Then
MsgBox (Space(45) & "ERROR!!!" & vbNewLine & "The Previous Work Week name you have entered is blank.")

ElseIf Tcellname = "" Then
MsgBox (Space(35) & "ERROR!!!" & vbNewLine & "The Template name you have entered is blank.")

ElseIf Err.Number = 1004 Then
Application.DisplayAlerts = False
Sheets(Tcellname & " (2)").Delete
Application.DisplayAlerts = True
MsgBox (Space(20) & "ERROR!!!" & vbNewLine & "The Sheet Name already exists.")

ElseIf Err.Number = 9 Then
MsgBox (Space(65) & "ERROR!!!" & vbNewLine & "The Template you are trying to copy does not exist or the previous worksheet you have entered does not exist.")



End If
Worksheets(1).Select
End Sub

 

[Norie]

What is the error?

 

[ARobb4]

there's actually no error, i jst want to make the code highlighted into:

ElseIf Err.Number = 9 Then
MsgBox (Space(65) & "ERROR!!!" & vbNewLine & "The the previous worksheet you have entered does not exist.")

ElseIf Err.Number = 9 Then
MsgBox (Space(65) & "ERROR!!!" & vbNewLine & "The Template you are trying to copy does not exist.")

but when i use that, the message box would display the text whose on top of the if statement =(
my senior told me not to use error handling anymore, but coding the other method that he told me to do was really confusing.

he told me that i dont need to use error handling, instead, i need to validate if the worksheet exists or not, if it does not exist, a msgbox must appear saying that the sheet im trying to copy does not exist..

 

[Norie]

What was the other method?

Was it checking if the sheet/template existed?

What exactly are you trying to do?

 

[ARobb4]

i've done it! =))

here's my code,

Sub CopyTemp()


'DECLARATION OF VARIABLES
Dim WWcellname As String
Dim Pcellname As String
Dim Tcellname As String


'SET CELL VALUES
WWcellname = Range("B1").Value
Pcellname = Range("B2").Value
Tcellname = Range("B3").Value


'PARA MAKITA KUNG EXSTING O HINDI ANG NAMES NG WORKSHEET
Dim i, o, u As Integer, ww, pw, tempp As Boolean
ww = False
pw = False
tempp = False
With ThisWorkbook
For i = 1 To .Sheets.Count
If .Sheets(i).Name = WWcellname Then
ww = True
Exit For
End If
Next i

For o = 1 To .Sheets.Count
If .Sheets(o).Name = Pcellname Then
pw = True
Exit For
End If
Next o

For u = 1 To .Sheets.Count
If .Sheets(u).Name = Tcellname Then
tempp = True
Exit For
End If
Next u
End With

'FOR WORK WEEK ERROR & SHEET CREATION
If ww = True Then
MsgBox ("The Sheet Name you have entered is alerady existing.")
ElseIf ww = False And pw = True And tempp = True Then
Sheets(Tcellname).Copy Before:=Sheets(Pcellname)
Sheets(Tcellname & " (2)").Name = WWcellname
End If

'FOR PREVIOUS WORK WEEK ERROR
If pw = False Then
MsgBox ("The Previous Work Week you have entered does not exist.")
End If

'FOR TEMPLATE ERROR
If tempp = False Then
MsgBox ("The Sheet that you are trying to replicate does not exist.")
End If




End Sub

The comments explains what i want to do, i'm sorry, i know this code is easy for u guys but i'm still a noob in vba, ahahaha
btw! thank you very much for noticing my thread