Using if else if to determine file location

[Needsomehelp10]

Hey guys,

I am trying to use a If ElseIf to determine where to save a file. For instance if A2 equals A, it will save in one location. If A2 equals B, the file will save in another location. Below is the VBA code I have so far. It will recognize the first if statement but will not carry out the ElseIf condition. Any help would be much appreciated thanks.



Sub

Dim Filename As String
Dim FilePath As String
If Range("A2") = "A" Then
Filename = ActiveSheet.Name & "December"
FilePath = "Insert File Path Here"
ActiveSheet.SaveAs Filename:=FilePath & "" & Filename, FileFormat:=51
ElseIf Range("A2") = "B" Then
Filename = ActiveSheet.Name & "December"
FilePath = "Insert Alternative File Path Here"
ActiveSheet.SaveAs Filename:=FilePath & "" & Filename, FileFormat:=51
EndIf
End Sub

 

[Joe4]

Other than the fact that you do not have a name for your procedure, there is nothing wrong with the structure of the code.

My guess is that the values you are checking for may not really match. Note that something as simple as a single space on the end will cause them not to match, i.e. "B " does not equal "B".

 

[Needsomehelp10]

Joe4,

I figured out what I was doing wrong, it was a simple mistake. I do have an additional question though. As of now, I have have the files being saved under the name Filename = ActiveSheet.Name & " TB - (10) October 2017". I want to define some variables so that I only have to change the month one time instead of having to go onto each if and else if statements and changing them. I have tried this but it is not working properly. I am new to this so I am probably just making a fairly simple fixable mistake. Thanks

Subfilelocation ()

Dim Filename As String
Dim FilePath As String
Dim Year As Integer
Dim Month As String
Month = "(10) October"
Year = 2017
If Range("A2") = "A" Then
Filename = ActiveSheet.Name & " TB - " & "Month" & "Year"
FilePath = "Insert File Path Here"
ActiveSheet.SaveAs Filename:=FilePath & "" & Filename, FileFormat:=51
ElseIf Range("A2") = "B" Then
Filename = ActiveSheet.Name & " TB - " & "Month" & "Year"
FilePath = "Insert Alternative File Path Here"
ActiveSheet.SaveAs Filename:=FilePath & "" & Filename, FileFormat:=51
EndIf
End Sub

 

[Joe4]

Here is the important thing to remember:
- anything enclosed in double-quotes is treated as literal text
- variables should never be enclosed in double-quotes

This little test will demonstrate this perfectly:

Sub Test()
    Dim mnth As String
    mnth = "October"
    MsgBox "Value returned with mnth in double-quotes: " & "mnth"
    MsgBox "Value returned with mnth NOT in double-quotes: " & mnth
End Sub

One other tip: Never choose reserved words (the name of functions, properties, objects, etc) as the name of your variables. It can cause ambiguity, unexpected results, and errors.
Since "Year" and "Month" are also the name of functions, choose different names (like I did above).