Microsoft Office Forums - View Single Post - [Solved] How to change save location and file to print name change?

macropod · 05-13-2012, 07:05 PM

Hi Albert,

To change the filename, you'd change 'ActiveSheet.Name' to point to the cell you want to get the name from (eg: 'ActiveSheet.Range("A1").Value'). Thus:

Code:

sFileName = InputBox("Save PDF to desktop as:", "Sheet '" & _
ActiveSheet.Name & "' to PDF...", ActiveSheet.Name)

might become:

Code:

sFileName = InputBox("Save PDF to desktop as:", _
ActiveSheet.Range("A1").Value & "' to PDF...", ActiveSheet.Name)

Do note, though, that certain characters can't be used in filenames and you'll get an error in that case. Extra code would be required to strip out invalid characters.

Similarly, to change the folder, you could replace the line:

Code:

sFolder = Environ("USERPROFILE") & "c:\Invoices\"

with something like:

Code:

GetFolder:
sFolder = Environ("USERPROFILE") & "c:\Invoices\"
sFolder = InputBox("Save PDF folder:", sFolder, "Save Folder")
If Dir(sFolder, vbDirectory) = "" Then
  MsgBox "Not a valid folder", vbCritical
  GoTo GetFolder
End If

to control the number of copies, you could change:

Code:

ActiveSheet.PrintOut

to:

Code:

ActiveSheet.PrintOut Copies:=3

or even:

Code:

ActiveSheet.PrintOut Copies:=InputBox("How many copies to print?", "Print Count", 3)