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.
VBA Code:
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