VBA to add to filename conditionally based on cell contents

[judgepax]

Trying to parse a single worksheet from a multi-worksheet workbook then save the new file. Got all that figured out, but if a specific cell (D51) = Solar, I need to add "_solar" to the end of the filename. Probably missing something obvious, but I can't Google someone trying to do similar. Please help.

    Dim FileExtStr As String
    Dim FileFormatNum As Long
    Dim Sourcewb As Workbook
    Dim Destwb As Workbook
    Dim TempFilePath As String
    Dim TempFileName As String

    Set Sourcewb = ActiveWorkbook

    'Copy the ActiveSheet to a new workbook
    ActiveSheet.Copy
    Set Destwb = ActiveWorkbook

    With Destwb
        FileExtStr = ".xlsx"
    End With

TempFilePath = "\\Partner\" & Range("$D$51") & "\"

TempFileName = Range("$C$17") & "_" & WorksheetFunction.Text(Range("$C$20").Value, "mm.dd.yy") & _
        If InStr(1, (Range("$D$51").Value), "Solar") > 0
        Then "_solar"     'Here is where I am having troubles
        End If

    With Destwb
        .SaveAs TempFilePath & TempFileName & FileExtStr, FileFormat:=FileFormatNum
        On Error Resume Next
       
        On Error GoTo 0
        .Close savechanges:=False
    End With

    'Delete the file you have send
    'Kill TempFilePath & TempFileName & FileExtStr

    With Application
        .ScreenUpdating = True
        .EnableEvents = True
    End With

End Sub

 

[mumps]

What do you want to do if D51 does not contain "Solar"?

 

[judgepax]

@mumps Don't add anything and leave filename as-is.

I would write the Excel formula as follows but I am not as well versed with VBA.

=IF(D51="Solar","_solar","")

 

[mumps]

Try:

Sub judgepax()
    With Application
        .ScreenUpdating = False
        .EnableEvents = False
    End With
    ActiveSheet.Copy
    If Range("D51") Like "*Solar*" Then
        With ActiveWorkbook
            .SaveAs "\\Partner\" & Range("$D$51") & "\" & Range("C17") & "_" & Format(Range("C20"), "mm.dd.yy") & "_solar.xlsx", FileFormat:=51
            .Close False
        End With
    Else
        With ActiveWorkbook
            .SaveAs "\\Partner\" & Range("$D$51") & "\" & Range("C17") & "_" & Format(Range("C20"), "mm.dd.yy") & ".xlsx", FileFormat:=51
            .Close False
        End With
    End If
    With Application
        .EnableEvents = True
        .ScreenUpdating = True
    End With
End Sub

 

[judgepax]

@mumps I see what you did! By making the whole .SaveAs part of the If statement issue is solved. Implemented & worked. Thank you!

 

[mumps]

You are very welcome.