VBA Save as PDF with Filename as Cell Value

Question

I am trying to save four sheets into a single PDF. The code below is what I have so far. When I use the ActiveSheet.Name command in the file name it works, however when I change it to a range for a cell that is dynamic it no longer works and errors out. Any help would be appreciated.

Sheets(Array("Dashboard Pg 1", "Dashboard Pg 2", "Dashboard Pg 3", _
    "Dashboard Pg 4")).Select
Sheets("Dashboard Pg 1").Activate
ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, Filename:= _
    "C:\Users\Allen\Desktop\Projects\" & ActiveSheet.Range("K17").Value & ".pdf" _
    , Quality:=xlQualityStandard, IncludeDocProperties:=True, IgnorePrintAreas _
    :=False, OpenAfterPublish:=False
Sheets("Summary").Select

What is the evaluated value of K17? Is it possible that the value includes characters which are not legal characters for a filename? — mechanical_meat
Add ".pdf" at end of filename (Filename:="C:\Users\Allen\Desktop\Projects\" & ActiveSheet.Range("K17").Value & ".pdf")? — PatricK
No adding .pdf hasn't helped either. It still asks me to debug when running — Allen
I suspect that you are using some form of date in K17 and then some cell formatting magic to get what you want as a filename. If you use .Value, the date will be represented as a short date with forward slashes which cannot be used in a filename. Use ActiveSheet.Range("K17").Text to get the formatted value from the cell. Make sure that the cell is wide enough to display the full formatted text or you will end up with ###### as your PDF filename. — user4039065
If "K17" is indeed a date, try something like this: Format(ActiveSheet.Range("K17"),"YYYY-MM-DD") — MMerry

Jur Pertin Jur Pertin · Accepted Answer · 2014-10-14T06:11:04

Try this:

Dim strFilename     As String
Dim rngRange        As Range

'Considering Sheet1 to be where you need to pick file name
Set rngRange = Worksheets("Sheet1").Range("K17")

'Create File name with dateStamp
strFilename = rngRange.Value & Format(Now(), "yyyymmdd hhmmss")

Sheets(Array("Dashboard Pg 1", "Dashboard Pg 2", "Dashboard Pg 3", "Dashboard Pg 4")).Select
Sheets("Dashboard Pg 1").Activate
ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, Filename:= _
    "C:\Users\Allen\Desktop\Projects\" & strFilename & ".pdf" _
    , Quality:=xlQualityStandard, IncludeDocProperties:=True, IgnorePrintAreas _
    :=False, OpenAfterPublish:=False

Sheets("Summary").Select

VBA Save as PDF with Filename as Cell Value

1 Answers