Create Macro to Save Active Sheet as PDF and save with in a Specific File Path

[catyanne]

Hi

I am trying to create a macro that picks up the file path that is entered on my Spreadsheet and save it as a PDF with in this File Path along with a specific file name:

The File Path is below - I need it to go to R1\R2\R3 (S:\Finance\F19\03 SEP 2018)

[TABLE="width: 250"]
<tbody>[TR]
[TD][/TD]
[TD]Q[/TD]
[TD]R[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]File Path[/TD]
[TD]S:\Finance[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]FY[/TD]
[TD]F19[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]Month[/TD]
[TD]03 SEP 2018[/TD]
[/TR]
</tbody>[/TABLE]


I currently have a Macro to save as PDF below:

Sub SavePDF()
ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, Filename:=Range("J1").Value & " " & Range("K1").Text _
    , Quality:=xlQualityStandard, IncludeDocProperties:=True, IgnorePrintAreas _
    :=False, OpenAfterPublish:=False


End Sub

How do I incorporate the specific file path with in this macro.

Thanks

 

[fhqwgads]

where is it saving to when you run it? documents?
i think your need to change the directory
try add ChDir "S:\Finance\F19\03 SEP 2018" at the start

Sub SavePDF()

ChDir "S:\Finance\F19\03 SEP 2018"

ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, Filename:=Range("J1").Value & " " & Range("K1").Text _
    , Quality:=xlQualityStandard, IncludeDocProperties:=True, IgnorePrintAreas _
    :=False, OpenAfterPublish:=False


End Sub

edit: using your cell references it should be

Sub SavePDF()

ChDir range("R1").value & "\" & range("R2").value & "\" and range("R3").value

ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, Filename:=Range("J1").Value & " " & Range("K1").Text _
    , Quality:=xlQualityStandard, IncludeDocProperties:=True, IgnorePrintAreas _
    :=False, OpenAfterPublish:=False


End Sub

hope that works

 

[catyanne]

Hi

Thanks for your reply. I can not add this as a directory. These cells are updated automatically based on the data within the spreadsheet (lookup tables based on dates and other info etc).

So I need it to pick up the data from those specific cells.

Thanks

where is it saving to when you run it? documents?
i think your need to change the directory
try add ChDir "S:\Finance\F19\03 SEP 2018" at the start

Sub SavePDF()

ChDir "S:\Finance\F19\03 SEP 2018"

ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, Filename:=Range("J1").Value & " " & Range("K1").Text _
    , Quality:=xlQualityStandard, IncludeDocProperties:=True, IgnorePrintAreas _
    :=False, OpenAfterPublish:=False


End Sub