VBA - Name PDF File, Should Be Easy

[kgallego]

Hi all,

I have what should be an easy question. I have code that prints a PDF from a spreadsheet. I want to change it so that the file name of the printed PDF references a cell plus some text.

For example, in cell Sheet1.cells(1,1).value I have "AC/DC". I want the below code to name the PDF "AC/DC IS AWESOME.pdf". Any help would be greatly appreciated!

Sub MySheetsIntoPdf99()


Dim wsA As Worksheet
Set wsA = ActiveSheet


Call ExportSheetsAsPdf99(ThisWorkbook.Path & "/IS AWESOME.pdf", wsA)


End Sub
Sub ExportSheetsAsPdf99(FileName As String, ParamArray exportedSheets() As Variant)


Dim WB As Workbook
Dim Sheet As Object
Dim i As Long


Application.ScreenUpdating = False
Application.DisplayAlerts = False

Set WB = Workbooks.Add
Set Sheet = exportedSheets(LBound(exportedSheets))
Sheet.Copy After:=WB.Sheets(1)
WB.Sheets(1).Delete

For i = LBound(exportedSheets) + 1 To UBound(exportedSheets)
exportedSheets(i).Copy After:=WB.Sheets(WB.Sheets.Count)
Next i

WB.ExportAsFixedFormat XlFixedFormatType.xlTypePDF, FileName, _
Quality:=xlQualityStandard, OpenAfterPublish:=False

WB.Close False

Application.DisplayAlerts = True
Application.ScreenUpdating = True

MsgBox "PDF file has been created"
Set WB = Nothing
Set Sheet = Nothing
End Sub


Thanks,
Kelsey

 

[Joe4]

I want the below code to name the PDF "AC/DC IS AWESOME.pdf".

You cannot use "/" in file names.

 

[kgallego]

Call it ACDC then. Just using this as an example.

 

[BigDawg15]

Try,

Untested

[COLOR=#574123][FONT=Tahoma]CallExportSheetsAsPdf99(ThisWorkbook.Path & Range("A1").value & " IS AWESOME.pdf", wsA)[/FONT][/COLOR]

Regards,

BigDawg15

 

[BigDawg15]

or try

Tested

Call ExportSheetsAsPdf99("C:\Users\yourid here\Desktop\" & Range("A1").Value & " IS AWESOME.pdf", wsA)

Will save file to your desktop or you can change the path to wherever you would like.

BigDawg15