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.
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.