VBA to name PDF using multiple cell references

AKRefugee

New Member
Joined
Apr 13, 2011
Messages
17
I have got the basics of generating a PDF from multiple sheets working ok. I also have it working to automatically save to and name the PDF based on manual input within the VBA. I am now trying to have the PDF file name generated using multiple cell references from one of the sheets. I am printing two sheets into one PDF file. Those sheets are "COVER SHEETS" AND "FAN BOOSTER SPOOL". Based the following code it saves it where the workbook was saved (good thing) and it gives me a file name of FAN BOOSTER SPOOL.

Sub CreatePDF2()
Dim ws As Worksheet

Set ws = ActiveSheet

If ThisWorkbook.Path = "" Then
MsgBox "Save File First"
Exit Sub
End If

'Order worksheets for proper pdf creation in order
Worksheets("COVER SHEETS").Move after:=Worksheets(Worksheets.Count)
Worksheets("FAN BOOSTER SPOOL").Move after:=Worksheets(Worksheets.Count)

With Sheets("FAN BOOSTER SPOOL")
Worksheets(Array("COVER SHEETS", "FAN BOOSTER SPOOL")).Select
ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, _
Filename:=ThisWorkbook.Path & "\FAN BOOSTER SPOOL.pdf", _
Quality:=xlQualityStandard, IncludeDocProperties:=True, _
IgnorePrintAreas:=False, OpenAfterPublish:=True
End With

ws.Select
End Sub

What I am trying to get it to do is to continue to save to the folder in which the workbook was saved but to name it as follows;

FAN BOOSTER SPOOL PN xxxx SN xxxx

I know I can hard code the FAN BOOSTER SPOOL PN portion in as well as the SN portion but am running into a wall on pulling xxxx data from cell references in sheet FAN BOOSTER SPOOL

In sheet FAN BOOSTER SPOOL the data is in the following cells:

A12 = "PN" D12 = xxxx (actual part number) A14 = "SN" D14 = xxxx (actual serial number)

This Macro will be assigned to a button on the PRINT PAGE

Thanks for all who review this post and can provide ideas, thoughts or suggestions.
 

Some videos you may like

Excel Facts

Repeat Last Command
Pressing F4 adds dollar signs when editing a formula. When not editing, F4 repeats last command.

John_w

MrExcel MVP
Joined
Oct 15, 2007
Messages
6,465
Replace this:
VBA Code:
Filename:=ThisWorkbook.Path & "\FAN BOOSTER SPOOL.pdf", _
with this:
VBA Code:
Filename:=ThisWorkbook.Path & "\FAN BOOSTER SPOOL PN " & Worksheets("FAN BOOSTER SPOOL").Range("D12").Value & " SN " & Worksheets("FAN BOOSTER SPOOL").Range("D14").Value & ".pdf", _
 

Watch MrExcel Video

Forum statistics

Threads
1,114,098
Messages
5,545,954
Members
410,713
Latest member
TaremyLunsil
Top