10

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
Allen
  • 105
  • 1
  • 1
  • 7
  • 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 Oct 13 '14 at 23:11
  • Add ".pdf" at end of filename (`Filename:="C:\Users\Allen\Desktop\Projects\" & ActiveSheet.Range("K17").Value & ".pdf"`)? – PatricK Oct 13 '14 at 23:23
  • No adding .pdf hasn't helped either. It still asks me to debug when running – Allen Oct 13 '14 at 23:30
  • 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. –  Oct 14 '14 at 00:50
  • If "K17" is indeed a date, try something like this: `Format(ActiveSheet.Range("K17"),"YYYY-MM-DD")` – MMerry Oct 14 '14 at 05:58

1 Answers1

16

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
Jur Pertin
  • 574
  • 4
  • 9
  • Thank you, that worked perfectly. Seting the range like you did was what fixed the problem. I appreciate everyone's help and responses. Also, Jeeped, thank you for mentioning the width of the cell as that was also needed. – Allen Oct 14 '14 at 23:57
  • Two years later this answer helped me with my own VBA question :) Thank you – mmenschig Apr 28 '16 at 20:56
  • Three years and two months later this answer has helped me solve a delicate VBA problem I faced and which I could not find a solution anywhere else. With very little modification, voilà! Thank you all ! – Luiz Vaughan Dec 12 '17 at 01:21
  • Six years and it is still working! Thanks @Jur Pertin! – RickyBelmont May 24 '22 at 02:08