The following is my code to print individual worksheets to .pdf file. How do
I change the following code to use file name as coded instead of the SAVE AS
box popping up? Thanks.

Sheets(Array("KAW Financial Summary", "Total Impact", _
"Gas Impact", "OIL Impact", "NGL Impact", "CO2 Impact")).Select
Application.ActivePrinter = "CutePDF Writer on CPW2:"
ActiveWindow.SelectedSheets.PrintOut Copies:=1, ActivePrinter:= _
"CutePDF Writer on CPW2:", Collate:=True

Dim myRange As Range
Set myRange = Worksheets("_").Range("a1")
stryearmo = Application.WorksheetFunction.Text(myRange, "yyyy-mm")
strdiryr = Application.WorksheetFunction.Text(myRange, "yyyy")
strmo = Application.WorksheetFunction.Text(myRange, "mm")

strfullfile = "U:\QEP\Revenue\Revenue_Estimates\Closing-" & strdiryr &
"\" & strdiryr & "_" & strmo & "\" & stryearmo & "EstimateImpact.pdf"
If Dir(strfullfile, vbNormal) < "" Then
Kill strfullfile
End If

ActiveWorkbook.SaveAs Filename:=strfullfile, _
CreateBackup:=False

You don't... Cute PDF is poping up the Save As request. Excel has nothing to
do with it. The only effective way to avoid that is to use a PDF writer that
has an API that you can access. There is an example here...

http://www.excelguru.ca/node/21
--
HTH...

Jim Thomlinson


"dallin" wrote:

The following is my code to print individual worksheets to .pdf file. How do
I change the following code to use file name as coded instead of the SAVE AS
box popping up? Thanks.

Sheets(Array("KAW Financial Summary", "Total Impact", _
"Gas Impact", "OIL Impact", "NGL Impact", "CO2 Impact")).Select
Application.ActivePrinter = "CutePDF Writer on CPW2:"
ActiveWindow.SelectedSheets.PrintOut Copies:=1, ActivePrinter:= _
"CutePDF Writer on CPW2:", Collate:=True

Dim myRange As Range
Set myRange = Worksheets("_").Range("a1")
stryearmo = Application.WorksheetFunction.Text(myRange, "yyyy-mm")
strdiryr = Application.WorksheetFunction.Text(myRange, "yyyy")
strmo = Application.WorksheetFunction.Text(myRange, "mm")

strfullfile = "U:\QEP\Revenue\Revenue_Estimates\Closing-" & strdiryr &
"\" & strdiryr & "_" & strmo & "\" & stryearmo & "EstimateImpact.pdf"
If Dir(strfullfile, vbNormal) < "" Then
Kill strfullfile
End If

ActiveWorkbook.SaveAs Filename:=strfullfile, _
CreateBackup:=False