VBA Help - saving 2 chart tabs to PDF with file name as cell ref

JaguarSean

New Member
Joined
Jun 5, 2017
Messages
31
Morning all

This has been really frustrating as I've almost managed to do what I want but it's not quite right. I'm no VBA expert. I pretty use Macro recorder for everything and piece it together.

I have a vast amount of data stored in a tab named "Stats & Plots".
My current Macro creates some charts/plots and moves them into 2 new chart tabs called "Charts for DVM Output PDF" and "Charts for DVM Output PDF 1". The Macro also resizes those plots so they all fit into one page. I then highlight both tabs and use 'save as' on a fixed directory and file name. At the end it all gets deleted so it doesn't clog the document up for the next user.

This works really well, I get a 2 page PDF with the charts I want in the given location and given name. 95% there! The document is cleaned and im happy (ish). This is the code im using to save the two chart tabs to PDF;

'Saves plots to PDF
Sheets(Array("Charts for DVM Output PDF", "Charts for DVM Output PDF 1")). _
Select
Sheets("Charts for DVM Output PDF").Activate
ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, Filename:= _
"C:\Username\DV Database Plots.pdf", Quality:=xlQualityStandard _
, IncludeDocProperties:=True, IgnorePrintAreas:=False, OpenAfterPublish:= _
True

What Id like to modify is the file name of the PDF to be a cell reference which is in "Stats & Plots" Cell L16. Currently, all files will be named 'DV Database Plots' which is a bit generic. I'd also like to designate the file path as this will be fixed.
This process will be run weekly with lots of variations and Cell L16 changes accordingly so it's important the PDF takes that cell references and takes both chart tabs together.

Help?!!?!

Many Thanks
Sean
 

Some videos you may like

Excel Facts

Which came first: VisiCalc or Lotus 1-2-3?
Dan Bricklin and Bob Frankston debuted VisiCalc in 1979 as a Visible Calculator. Lotus 1-2-3 debuted in the early 1980's, from Mitch Kapor.

John_w

MrExcel MVP
Joined
Oct 15, 2007
Messages
6,465
Replace "C:\Username\DV Database Plots.pdf" with "C:\Username\" & Worksheets("Stats & Plots").Range("L16").Value, assuming L16 also contains the ".pdf" extension.
 

JaguarSean

New Member
Joined
Jun 5, 2017
Messages
31
Replace "C:\Username\DV Database Plots.pdf" with "C:\Username\" & Worksheets("Stats & Plots").Range("L16").Value, assuming L16 also contains the ".pdf" extension.

L16 only contains the name of the file.

Would L16 need to look like this?;

C:\Username\Filename of my choice
 

JaguarSean

New Member
Joined
Jun 5, 2017
Messages
31
This is my new code s per your suggestion;

'Saves plots to PDF
Sheets(Array("Charts for DVM Output PDF", "Charts for DVM Output PDF 1")). _
Select
Sheets("Charts for DVM Output PDF").Activate
ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, Filename:= _
"C:\username\" & Worksheets("Stats & Plots").Range("L16").Value, Quality:=xlQualityStandard _
, IncludeDocProperties:=True, IgnorePrintAreas:=False, OpenAfterPublish:= _
True

L16 contains the following;

DV Status Plots Wk41.pdf

This gives an error and falls over
 

John_w

MrExcel MVP
Joined
Oct 15, 2007
Messages
6,465

ADVERTISEMENT

What's the error? You'll get an error if the destination PDF is already open.

This works for me:
VBA Code:
    'Saves plots to PDF
    Sheets(Array("Charts for DVM Output PDF", "Charts for DVM Output PDF 1")).Select
    ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, fileName:="C:\Temp\" & Worksheets("Stats & Plots").Range("L16").Value, _
        Quality:=xlQualityStandard, IncludeDocProperties:=True, IgnorePrintAreas:=False, OpenAfterPublish:=True
    Worksheets("Stats & Plots").Activate
 

Watch MrExcel Video

Forum statistics

Threads
1,114,035
Messages
5,545,638
Members
410,696
Latest member
JTrehan
Top