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

[JaguarSean]

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

 

[John_w]

Replace "C:\Username\DV Database Plots.pdf" with "C:\Username\" & Worksheets("Stats & Plots").Range("L16").Value, assuming L16 also contains the ".pdf" extension.

 

[JaguarSean]

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]

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]

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

This works for me:

    '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

 

[JaguarSean]

Works a treat now, not sure what I did wrong!

 

[JaguarSean]

Thankyou very much