Having problems with multiple If - End If Statements

[Logan]

I can't seem to figure out how to use more than one If -
End If statements in my Save As macro
Here is the code. I keep getting an error.

Sub Save_As()
Dim FName1 As String, FName2 As String, FName3 As String,
Fullname As String
FName1 = "CK0"
FName2 = Range("AU2").Value & "-"
FName3 = Range("J4").Value
Fullname = FName1 & FName2 & FName3
Application.DisplayAlerts = False
ChDir "C:\Arrest"
If ActiveSheet.Range("BJ31").Value = "No" Then
Worksheets(Array("Sheet3", "Sheet5", "Sheet8")).Delete
ElseIf ActiveSheet.Range("BJ31").Value = "Yes" Then
Worksheets(Array("Sheet4", "Sheet9", "Sheet13")).Delete
End If
If ActiveSheet.Range("N32").Value = "Adult" Then
Worksheets(Array("Sheet7", "Sheet12", " Sheet15",
"Sheet16", "Sheet23")).Delete
ElseIf ActiveSheet.Range("N32").Value = "Youth" Then
Worksheets(Array("Sheet11", "Sheet14", "Sheet17",
"Sheet22")).Delete
End If
ActiveWorkbook.SaveAs Fullname, _
FileFormat:=xlNormal, _
CreateBackup:=False, _
Accessmode:=xlShared
MsgBox "Saved to " & CurDir & " - " & Fullname

End Sub

Any help would be appreciated

 

[Bob Phillips]

Logan,

Is this what you mean?

Sub Save_As()
Dim FName1 As String, FName2 As String
Dim FName3 As String, Fullname As String
FName1 = "CK0"
FName2 = Range("AU2").Value & "-"
FName3 = Range("J4").Value
Fullname = FName1 & FName2 & FName3
Application.DisplayAlerts = False
ChDir "C:\Arrest"
With ActiveSheet
If .Range("BJ31").Value = "No" Then
Worksheets(Array("Sheet3", "Sheet5", "Sheet8")).Delete
ElseIf .Range("BJ31").Value = "Yes" Then
Worksheets(Array("Sheet4", "Sheet9", "Sheet13")).Delete
ElseIf .Range("N32").Value = "Adult" Then
Worksheets(Array("Sheet7", "Sheet12", " Sheet15", "Sheet16",
"Sheet23")).Delete
ElseIf .Range("N32").Value = "Youth" Then
Worksheets(Array("Sheet11", "Sheet14", "Sheet17",
"Sheet22")).Delete
End If
End With
ActiveWorkbook.SaveAs Fullname, _
FileFormat:=xlNormal, _
CreateBackup:=False, _
Accessmode:=xlShared
MsgBox "Saved to " & CurDir & " - " & Fullname

End Sub

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

 

[Logan]

The second part of the Else If statement won't (refers to
cell N32) delete the sheets.
I didn't know if it had to be a different statement because
they refer to different cells? One reference is not
conditional of the other.

 

[Bob Phillips]

Logan,

If you use

If ...
Elseif ...
Elseif....
End If

or even

If ,,,,
Else
If ....
Else
If ....
End If
End If

as soon as a condition is met, then the others will be ignored. If you have
two independent conditions to test for, it is necessary to separate them

If condition1 Then
...
Else
...
End If

If condition2 Then
....
Else
...
End If

Does that all help?


--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

 

[Logan]

That helped Bob.
Thanks again

-----Original Message-----
Logan,

If you use

If ...
Elseif ...
Elseif....
End If

or even

If ,,,,
Else
If ....
Else
If ....
End If
End If

as soon as a condition is met, then the others will be ignored. If you have
two independent conditions to test for, it is necessary to separate them

If condition1 Then
...
Else
...
End If

If condition2 Then
....
Else
...
End If

Does that all help?


--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)




.