How to change save location and file to print name change?

macropod · #2 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)

Similar Threads
Thread	Thread Starter	Forum	Replies	Last Post
change the view to final, my change bars disappear. I need them visible	anna.a.julin	Word	1	03-01-2012 08:05 PM
Outlook 2010 - Change default data file.	siouxnami	Outlook	4	01-26-2012 12:24 PM
Change style of 'open file' window?	markg2	Word	0	07-08-2011 11:15 AM
PowerPoint 2003 - Change default location for Insert Picture	Fee	PowerPoint	0	05-12-2011 02:15 AM
[PowerPoint 2010] Change font for all slides in an exist *.PPTX file	LongTTH	PowerPoint	0	12-15-2010 02:07 AM