I've been using excel to save my Worksheet as both PDF and as a .XLS separately based on a cell value, but it saves in the same folder as the parent file. I have been trying to find a way to use VBA to save these files under various paths, and these paths are references in cell values based on a data validation tool.
For Example: Path:- D:\Collections\March\Region 1\ - Is referenced in cell D3
File Name: LN,FN (mm/dd/yy) - Is referenced in cell D4
The bold values keep changing based on Lookup formulas in Excel. The current code I am using to save to .XLS is:
Sub SaveASXLS()
Dim FName As String
Dim FPath As String
FPath = "D:\Collections\March\Region 5\Cerritos"
FName = Sheets("Sheet1").Range("D4").Text
ThisWorkbook.SaveAs Filename:=FPath & "\" & FName, FileFormat:=56
End Sub
The FPATH= "File Location" I got it to work with a fixed location, but have to change it everytime I switch my Region or City.
And the code I am using for exporting the file to PDF is:
Sub SavePDF()
ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, Filename:=Range("D4").Value _
, Quality:=xlQualityStandard, IncludeDocProperties:=True, IgnorePrintAreas _
:=False, OpenAfterPublish:=True
End Sub
I am using both separately one as a Module and one a a basic macro, is there also a way to combine both these functions together.
Thanks alot in advance for your help.