Save file as pdf with a file name based on a formula

**Ricky Wilko** · 12-07-2011, 12:08 AM

I have a worksheet (called Invoice Generator) that generates a pdf invoice from a database of records.

I want to save each invoice as a pdf file to be e-mailed out to customers.

I want to save each pdf file with a different name. I have a formula in a cell (File_Name) that specifies the file name based on a date and reference number etc.

The macro I have is as follows:

Application.Goto Reference:="File_Name"
Selection.Copy
Application.Goto Reference:="File_Name_Value"
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
MyName = File_Name_Value
ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, Filename:=MyName, _
Quality:=xlQualityStandard, IncludeDocProperties:=True, IgnorePrintAreas _
:=False, OpenAfterPublish:=False
Application.Goto Reference:="Record_Number"

File_Name is the formula that has the file name including path etc.
I copy paste value this into File_Name_Value and then want to use this as the filename for the pdf file.
When I run the macro it saves the file under the worksheet name and not the new pdf file name.

Thanks for any tips.

**Dirigo** · 12-07-2011, 01:29 AM

This is probably more a tip than an all-out solution, but it would appear that the variable MyName is being assigned a null value, as you don't define the variable it is being assigned, i.e., File_Name_Value.

I believe you wish to assign the var MyName to the value of the cell that you have named as "File_Name_Value" which I assume is a named range. If so, try replacing the line:

MyName = File_Name_Value

with


MyName = Range("File_Name_Value").Value

**Ricky Wilko** · 12-07-2011, 05:54 PM

Brilliant - works perfectly. Thanks for your help.

**Dirigo** · 12-08-2011, 01:02 AM

Glad to help out an Aussie!

BTW, your code could be much more streamlined, avoiding the copy and pasting, which is somewhat inelegant, but if it satisfies your purposes and you're not interested in lean, efficient VBA programming, then why let perfect be the enemy of good?

Welcome to the forum and congrats on finding a VBA solution to your needs.

**Ricky Wilko** · 12-08-2011, 08:50 PM

Elegance and simplicity I like. Not being a progammer I was just trying to replicate the keystrokes I would go through when doing it manually.

I have now simplified the macro to this which also works.

Application.Goto Reference:="Record_Number"
MyName = Range("File_Name").Value
ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, Filename:=MyName, _
Quality:=xlQualityStandard, IncludeDocProperties:=True, IgnorePrintAreas _
:=False, OpenAfterPublish:=False
Application.Goto Reference:="Record_Number"

Thanks again for your help.