Save as PDF in file location referenced in cell.

[Tom Allen]

Hi I'm a little stuck and would greatly appreciate any assistance. I have looked over similar threads regarding this topic but couldn't seem to find what I was looking for.

I currently have the following code that works great for saving as PDF with a file name referenced from certain cells.

Sub Create_PDF_Invoice()
Dim wsh As Worksheet, vWshs, vWshName

vWshs = Array("Standard Template")
With ActiveWorkbook
    For Each vWshName In vWshs
        .Worksheets(vWshName).ExportAsFixedFormat Type:=xlTypePDF, FileName:= _
            "D:\Finance\Invoices Sent\" & "Invoice #" & .Worksheets(vWshName).Range("H5").Value & " - " & .Worksheets(vWshName).Range("H8").Value & " " & .Worksheets(vWshName).Range("A16").Value, Quality:=xlQualityStandard, _
            IncludeDocProperties:=True, IgnorePrintAreas:=False, OpenAfterPublish:=False
    Next vWshName
End With
End Sub

The above code saves as PDF in the location D:\Finance\Invoices Sent, it saves the PDF with a name in the following format for example Invoice #100 - CLIENTNAME Invoice Reference

I need to adapt it so that the folder is saves in is dynamic based on what is entered in cell H8. Cell H8 contains the client name and I have folders set up for each client where I would like to automatically save their invoices.

So for example it would save in D:\Finance\Invoices Sent\CLIENTNAME

 

[Andrew Fergus]

Hi Tom

Without testing this, and assuming the folder exists, I believe you need to change this part:
"D:\Finance\Invoices Sent\" & "Invoice #"

to this:
"D:\Finance\Invoices Sent\" & .Worksheets(vWshName).Range("H8").Value & "\Invoice #"

keep everything after that exactly as you had it before.

Andrew