Print to PDF Macro, or possibly more

[Cliffork]

I have a compilation file that we fill in calibration forms for a device on. There are a handful of sheets in the workbook, but every since one has a sheet named "Cal Form" that I am trying to automate printing a PDF of, with the name being pulled from two cells in said sheet (C8 and K8).

If possible I'm also trying to find some way to print PDF's of dozens of existing Cal Forms as my customers request them all the time, and getting them is tedious. I'm not sure what the best way to do this would be.
I have hundreds of these files and it would honestly be convenient to just be able to print every single one to PDF automatically somehow.

I've tried searching the forums and just googling but it seems everyone is trying to do something differently and I've tried 2 so far with no luck on my end. I've also tried recording a macro of saving it as a PDF but it doesn't appear to function.

Code for my recorded macro that doesn't seem to work is

Sub PrintToPDF()
'
' PrintToPDF Macro
'
' Keyboard Shortcut: Ctrl+q
'
    ChDir "Z:\Shared\EEW\Lab\Cal Cert PDF"
    ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, Filename:= _
        "Z:\Shared\EEW\Lab\Compilation Documents\Envision\Meter Repair Compilation File -Template.pdf" _
        , Quality:=xlQualityStandard, IncludeDocProperties:=True, IgnorePrintAreas _
        :=False, OpenAfterPublish:=False
End Sub

Sheet is below in case it helps.

Meter Repair Compilation File -TemplateTEST.xlsm
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V	W	X	Y	Z	AA	AB	AC	AD	AE
2	Testing and Calibration Report
3
4
7
8	Date:		10/29/2024				Meter SN#:				1234567B				Model #:			ENV200				RMA#:			0500-1234
9		Lab Testing Performed
10			Bluetooth						Battery Charger						Available Pressure								CH4 Sensor					H2S Sensor
11			Firmware						Battery health						Impact Pressure								CO2 Sensor					CO Sensor
12			Thermistor						Static Pressure						Seal Integrity								O2 Sensor					H2 Sensor
13
14	Calibration Results:
15	Gas							Target Value 1			Analyzer Value 1			Target Value 2			Analyzer Value 2			Calibration Gas Bottle Lot#							Analytical Uncertainty
16	A	20.9% O2						20.9% O2			0.00			-			-			32-401209938-1							± 0.02% abs
17	B	11% O2						11% O2			0.00			-			-			32-401919744-1							± 0.02% abs
18	C	50% CH4			35% CO2			50% CH4			0.00			35% CO2			0.00			141-403023316-1							± 0.02% abs
19	D	15% CH4			15% CO2			15% CH4			0.00			15% CO2			0.00			141-402762057-1							± 2% rel
20	E	40% CH4			60% CO2			40% CH4			0.00			60% CO2			0.00			141-403007833-1							± 1% rel
21	F	100% CO2						-			-			100% CO2			0.00			25-401897982-1							- 0.01% abs
22	G	100% CH4						100% CH4			0.00			-			-			141-402357134-1							- 0.5% abs
27
28	Zero Gas Value							CH4				CO2				O2				H2				H2S				CO
29	L	99.999% N2						0.00				0.00				0.00				0.00				0.00				0.00
30	Calibration Gas directly traceable to NIST ASTM Class 1 weights and/or NIST gas mixture reference materials
31	Pressure Sensors: (All measurements made in"WC)
32	Low Pressures																High Pressures
33	Sensor			Target Value 1			Analyzer Value 1			Target Value 2			Analyzer Value 2				Sensor			Target Value 1			Analyzer Value 1			Target Value 2			Analyzer Value 2
34	Applied			0.000			0.00			-4.50			0.00				Available			0.000			0.00			-100.00			0.00
35	Diff.			0.000			0.000			4.500			0.000				Diff.			0.000			0.00			20.000			0.00
36																	Applied			0.000			0.000			-100.00			0.000
37	This device has passed testing and calibration procedures as specified by Elkins Earthworks, LLC.
38
39	Calibrated by (Signature):								KC
40
41	At Elkins Earthworks we appreciate your business. We strive for a standard 10 working day turn arround on all calibrations and repairs from the time we have received your instrument. Your instrument has been serviced in 32559 days. We hope you appreciate our dedication to service and quality.
42
43
44	Thanks,																Notes as to why service exceeded 10 day turn
45
46
47
48
49	President
50	Elkins Earthworks, LLC.
51
52
53
54
Cal Form

Cell Formulas
Range	Formula
K8	K8	=('Customer Info'!B15)
R8	R8	='Customer Info'!B16
Y8	Y8	=('Customer Info'!B13)
B10	B10	=IF(ISNUMBER(SEARCH("X",'Final Insp.'!A13)),"P", "")
B11	B11	=IF(ISNUMBER(SEARCH("X",'Final Insp.'!A11)),"P", "")
B12	B12	=IF(ISNUMBER(SEARCH("X",'Final Insp.'!A68)),"P", "")
H10	H10	=IF(ISNUMBER(SEARCH("X",'Final Insp.'!A10)),"P", "")
H11	H11	=IF(ISNUMBER(SEARCH("",'Final Insp.'!A28)),"P", "")
H12	H12	=IF(ISNUMBER(SEARCH("",'Final Insp.'!A28)),"P", "")
N10	N10	=IF(ISNUMBER(SEARCH("",'Final Insp.'!A28)),"P", "")
N11	N11	=IF(ISNUMBER(SEARCH("",'Final Insp.'!A28)),"P", "")
N12	N12	=IF(ISNUMBER(SEARCH("X",'Final Insp.'!A52)),"P", "")
V10	V10	=IF(ISNUMBER(SEARCH("",'Final Insp.'!A53)),"P", "")
V11	V11	=IF(ISNUMBER(SEARCH("",'Final Insp.'!A53)),"P", "")
V12	V12	=IF(ISNUMBER(SEARCH("",'Final Insp.'!A53)),"P", "")
AA10	AA10	=IF(ISNUMBER(SEARCH("",'Final Insp.'!A53)),"P", "")
AA11	AA11	=IF(ISNUMBER(SEARCH("",'Final Insp.'!A53)),"P", "")
AA12	AA12	=IF(ISNUMBER(SEARCH("",'Final Insp.'!A53)),"P", "")
K16	K16	='Final Insp.'!F35
K17	K17	='Final Insp.'!F42
K18:K20,K22	K18	='Final Insp.'!D37
Q18:Q21	Q18	='Final Insp.'!E37
H29	H29	='Final Insp.'!D36
L29	L29	='Final Insp.'!E36
P29	P29	='Final Insp.'!F36
T29	T29	='Final Insp.'!D47
X29	X29	='Final Insp.'!F48
AB29	AB29	='Final Insp.'!E46
G34:G35	G34	='Final Insp.'!D25
M34:M35	M34	='Final Insp.'!I25
W34	W34	='Final Insp.'!E24
W35	W35	='Final Insp.'!E26
W36	W36	='Final Insp.'!E25
AC34	AC34	='Final Insp.'!J24
AC35	AC35	='Final Insp.'!J26
AC36	AC36	='Final Insp.'!J25
A41	A41	="At Elkins Earthworks we appreciate your business. We strive for a standard 10 working day turn arround on all calibrations and repairs from the time we have received your instrument. Your instrument has been serviced in " & NETWORKDAYS('Customer Info'!B14,C8,'Drop Down lists'!M2:M105)-1 & " days. We hope you appreciate our dedication to service and quality."

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
AA10:AA12	Expression	=RIGHT(UPPER('Customer Info'!$B$15),1)="D"	text	NO
T28:AE29	Expression	=RIGHT(UPPER('Customer Info'!$B$15),1)="D"	text	NO
T28:AE28	Expression	=RIGHT(UPPER('Customer Info'!$B$15),1)="D"	text	NO
AA10:AE12,T28:AE29	Expression	=RIGHT(UPPER('Customer Info'!$B$15),1)="D"	text	NO
I39	Cell	contains a blank value	text	NO
C8	Cell	contains a blank value	text	NO

Cells with Data Validation
Cell	Allow	Criteria
I39:Z39	List	='Drop Down lists'!$A$1:$A$6

 

[Cliffork]

Update: I got it to partially work recording it differently. Now I have the code below. I can open this file and run the macro in any file I want to make a PDF for, but it keeps the name as the original file name for some reason. If I could make the name pull from C8/K8 I think I'd be mostly set(unless someone can help me think of a way to export all my files this way in one fell swoop!)

Sub exportToPDF()
'
' exportToPDF Macro
'
' Keyboard Shortcut: Ctrl+w
'
    ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, Filename:= _
        "Z:\Shared\EEW\Lab\Cal Cert PDF\Meter Repair Compilation File -Template", _
        Quality:=xlQualityStandard, IncludeDocProperties:=True, IgnorePrintAreas _
        :=False, OpenAfterPublish:=False
End Sub

 

[Cliffork]

I made a little more slight progress, I made the file name save as the serial number, but I still need to figure out how to make it save as the serial number and the date it was created so I can create one for multiple years without needing to rename them.

Sub exportToPDF()
'
' exportToPDF Macro
'
' Keyboard Shortcut: Ctrl+q
'
    ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, Filename:= _
        "Z:\Shared\EEW\Lab\Cal Cert PDF\" & Range("K8").Value, _
        Quality:=xlQualityStandard, IncludeDocProperties:=True, IgnorePrintAreas _
        :=False, OpenAfterPublish:=False
End Sub

 

[mumps]

Change the folder path to where all your files are located (in red) and the file extension of those files (in blue) to suit your needs.

Sub exportToPDF()
    Application.ScreenUpdating = False
    Dim wkbSource As Workbook
    Set wkbDest = ThisWorkbook
    Const strPath As String = "C:\Test\"
    ChDir strPath
    strExtension = Dir(strPath & "*.xlsx")
    Do While strExtension <> ""
        Set wkbSource = Workbooks.Open(strPath & strExtension)
        With wkbSource.ActiveSheet
                .ExportAsFixedFormat Type:=xlTypePDF, Filename:= _
                "Z:\Shared\EEW\Lab\Cal Cert PDF\" & .Range("C8") & " " & .Range("K8"), _
                Quality:=xlQualityStandard, IncludeDocProperties:=True, IgnorePrintAreas _
                :=False, OpenAfterPublish:=False
            .Close savechanges:=False
        End With
        strExtension = Dir
    Loop
    Application.ScreenUpdating = True
End Sub

 

[Cliffork]

Change the folder path to where all your files are located (in red) and the file extension of those files (in blue) to suit your needs.

Sub exportToPDF()
    Application.ScreenUpdating = False
    Dim wkbSource As Workbook
    Set wkbDest = ThisWorkbook
    Const strPath As String = "C:\Test\"
    ChDir strPath
    strExtension = Dir(strPath & "*.xlsx")
    Do While strExtension <> ""
        Set wkbSource = Workbooks.Open(strPath & strExtension)
        With wkbSource.ActiveSheet
                .ExportAsFixedFormat Type:=xlTypePDF, Filename:= _
                "Z:\Shared\EEW\Lab\Cal Cert PDF\" & .Range("C8") & " " & .Range("K8"), _
                Quality:=xlQualityStandard, IncludeDocProperties:=True, IgnorePrintAreas _
                :=False, OpenAfterPublish:=False
            .Close savechanges:=False
        End With
        strExtension = Dir
    Loop
    Application.ScreenUpdating = True
End Sub

It seems to run fine, but it doesn't create any PDF's, unless I'm just looking in the wrong place. Looks like you called out to have them save in my Z: drive folder I had created for it, but nothing is there.

Also, is there anything in there that will tell it to specifically print the Sheet "Cal Form"?

 

[mumps]

Did you change the file path in red to match the location of all your files?

 

[Cliffork]

Did you change the file path in red to match the location of all your files?

My files are in a hundred different folders based on what customer owns them. I made a Test folder on my C: drive to match and put two test files in it, and changed the file type to .xlsm. That is the only change I made.

I'm also not sure the file name code is working correctly because I tried to add it into my individual macro to change the name to "date serial.pdf" but it just fails the macro every time, the .Range gets highlighted and gives a "compile error: Invalid or unqualified reference"

Sub exportToPDF()
'
' exportToPDF Macro
'
' Keyboard Shortcut: Ctrl+q
'
    ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, Filename:= _
        "Z:\Shared\EEW\Lab\Cal Cert PDF\" & .Range("C8") & " " & .Range("K8"), _
        Quality:=xlQualityStandard, IncludeDocProperties:=True, IgnorePrintAreas _
        :=False, OpenAfterPublish:=False
End Sub

 

[Cliffork]

I'm also not sure the file name code is working correctly because I tried to add it into my individual macro to change the name to "date serial.pdf" but it just fails the macro every time, the .Range gets highlighted and gives a "compile error: Invalid or unqualified reference"

Sub exportToPDF()
'
' exportToPDF Macro
'
' Keyboard Shortcut: Ctrl+q
'
    ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, Filename:= _
        "Z:\Shared\EEW\Lab\Cal Cert PDF\" & .Range("C8") & " " & .Range("K8"), _
        Quality:=xlQualityStandard, IncludeDocProperties:=True, IgnorePrintAreas _
        :=False, OpenAfterPublish:=False
End Sub

I've found this is some kind of Syntax error on my end but it confuses me, it's apparently just getting a run time error 1004 when trying to reference cell C8 even though there's nothing special about that cell. I have no idea what's wrong with it.

 

[mumps]

Try removing the dots to the left of “Range”.

 

[Cliffork]

Try removing the dots to the left of “Range”.

I tried that as well. Still no good.
I think it's something to do with the fact that I have a date in cell C8. Maybe it doesn't like the / since file names can't have forward slashes. I tried formatting the date to 10-29-24 as well though and it still gives the same error.
It works if I just put a single letter in the cell.