CODEWITHSUNDEEP
vbaexcel
MrsAdmin
MrsAdmin

Reputation: 548

Excel 2010: Best use of CASE or IF to define SAVEAS location (VBA)

Program: Excel 2010
Experience: Basic

Question
I am wanting to save my workbook sheet (and generated .pdf) in locations dependent on a cell value, rather than writing 5 Subs, I want to write one using either IF or CASE. I do have a static save location (dropbox), however I also need to save duplicates in the respective Cell locations.

I can't get the syntax correct to get either working. 

Sub saveManID()
    Dim sDB As String
    Dim sMDocs As String
    Dim sMBus As String
    Dim sName As String
    Dim sSel As String
    Dim sMan As String

    'define file name
    sName = Sheets("Statement").Range("B52").Text

    'define location name
    sDB = "E:\location dropbox\"
    sMDocs = "D:\My Documents\"
    sMBus = "D:\location alt\"

    '---- Either IF or CASE to define the SAVEAS location ----'  
    If Sheets("Statement").Range("J2").Text = "3" Then
        sMan = "G:\location\folder3\"

    If Sheets("Statement").Range("J2").Text = "4" Then
        sMan = "G:\location\folder4\"

    '---- end ----'   

    ActiveWorkbook.SaveAs Filename:=sMan & sName & Format(Date, "YYYYMMDD") & ".xls",_  
     FileFormat:=52, ReadOnlyRecommended:=False, CreateBackup:=False    

    Sheets("Statement").Range("A1:G49").ExportAsFixedFormat Type:=xlTypePDF, Filename:=sMan & sName & ".pdf", _
        Quality:=xlQualityStandard, IncludeDocProperties:=True, IgnorePrintAreas:=False, OpenAfterPublish:=True


        End if
    End if
End Sub

I need to get the Range("J2") to define location from the cell value & I need to create the name based on another cell value.

The Sub runs fine if I exclude the IF, but it means I have to have the code duplicated and a button assigned for each value.

To clarify - the Cell Value Range("J2") will determine where the file saves to, in the example above it is in the IF statement, which does not work.

Upvotes: 0

Views: 135

Answers (1)

Automate This
Automate This

Reputation: 31364

To fix what you have written try moving your End If before your common code like this:

If Sheets("Statement").Range("J2").Text = "3" Then
    sMan = "G:\location\folder3\"
End If

If Sheets("Statement").Range("J2").Text = "4" Then
    sMan = "G:\location\folder4\"
End if

ActiveWorkbook.SaveAs Filename:=sMan & sName & Format(Date, "YYYYMMDD") & ".xls",_  
 FileFormat:=52, ReadOnlyRecommended:=False, CreateBackup:=False    

Sheets("Statement").Range("A1:G49").ExportAsFixedFormat Type:=xlTypePDF, Filename:=sMan & sName & ".pdf", _
    Quality:=xlQualityStandard, IncludeDocProperties:=True, IgnorePrintAreas:=False, OpenAfterPublish:=True

Or like this:

If Sheets("Statement").Range("J2").Text = "3" Then
    sMan = "G:\location\folder3\"
ElseIf Sheets("Statement").Range("J2").Text = "4" Then
    sMan = "G:\location\folder4\"
End if

Or like this:

myVal = Sheets("Statement").Range("J2").Text
If myVal = "3" Then
    sMan = "G:\location\folder3\"
ElseIf myVal = "4" Then
    sMan = "G:\location\folder4\"
End if

To use a Select Case re-write it like this:

Select Case Sheets("Statement").Range("J2").Text
      Case "3"
           sMan = "G:\location\folder3\"

      Case "4"
           sMan = "G:\location\folder4\"

      Case Else
           'set sMan to your default dropbox location here
End Select

Upvotes: 3

Related Questions