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]

I don't get the Save As window so I can't reproduce that problem. You have a link to another file in each of the two workbooks. In the Data menu at the top, click on Workbook Links, break the link and save the file and close it. You have to do this for each file. Then try the macro again.

Looks to have been all it is, unfortunate because that's probably on every file I have, I don't even know why its linking to it, but I'm sure I made some mistake 2-3 years ago when I added that tab for a specific test. Thank you though! You have been incredibly helpful!

 

[mumps]

My pleasure.