Excel VBA Code to save each iteration of List in a separate PDF File

[Angel_Code Help]

Hi Everyone,

I'm extremely new to VBA coding.

My excel sheet has a data validation list in cell G4. Depending on the list value selected, the cells below use index match to get values from another sheet.

I need to figure out how to save a pdf version of this tab for every single list value.

This is the code I've pieced together so far from similar threads on this board, but this only saves a PDF file for the last option in the validation list instead of ALL the options. It's driving me crazy cause other people have claimed it works

Thanks for the help!

Public Sub Create_PDFs()

Dim dataValidationCell As Range, dataValidationListSource As Range, dvValueCell As Range
Dim dropdownValue As String
Dim PDFfile As String, i As Long
Dim destinationFolder As String
destinationFolder = ThisWorkbook.Path
'destinationFolder = "C:\path\to\folder" 'Or specific folder
If Right(destinationFolder, 1) <> "" Then destinationFolder = destinationFolder & ""

'Cell containing data validation in-cell dropdown
Set dataValidationCell = Worksheets("Summary_Page").Range("g4")
'Source of data validation list
Set dataValidationListSource = Evaluate(dataValidationCell.Validation.Formula1)
'Create PDF for each data validation value

For Each dvValueCell In dataValidationListSource
dataValidationCell.Value = dvValueCell.Value

With dataValidationCell.Worksheet
.ExportAsFixedFormat Type:=xlTypePDF, Filename:=destinationFolder & dvValueCell.Value & ".PDF", _
Quality:=xlQualityStandard, IncludeDocProperties:=True, IgnorePrintAreas:=False, OpenAfterPublish:=False
End With
Next
End Sub

 

[HaHoBe]

Hi,

this code works for me - please give it a try:

Public Sub Create_PDFs1612314()
'https://www.mrexcel.com/board/threads/excel-vba-code-to-save-each-iteration-of-list-in-a-separate-pdf-file.1219556/

Dim rngDVCell           As Range      'cell with DV
Dim rngDVListSource     As Range      'list pf values for rngDVCell
Dim rngDV_Value         As Range      'each single value in rngDVListSource
Dim strPathFolder       As String     'drive and path to folder for save, should have a trailing \ for windows

strPathFolder = ThisWorkbook.Path
'strPathFolder = "C:\path\to\folder" 'Or specific folder
'///check for the trailing backslash and add it if necessary
If Right(strPathFolder, 1) <> Application.PathSeparator Then strPathFolder = strPathFolder & Application.PathSeparator

'Cell containing data validation in-cell dropdown
Set rngDVCell = Worksheets("Summary_Page").Range("G4")
'Source of data validation list
Set rngDVListSource = Evaluate(rngDVCell.Validation.Formula1)
'Create PDF for each data validation value

For Each rngDV_Value In rngDVListSource
  rngDVCell.Value = rngDV_Value.Value
  
  '/// specify the sheet and range to print
  With Worksheets("Summary_Page").Range("A1:G29")
      .ExportAsFixedFormat Type:=xlTypePDF, _
                            Filename:=strPathFolder & rngDV_Value.Value & ".PDF", _
                            Quality:=xlQualityStandard, _
                            IncludeDocProperties:=True, _
                            IgnorePrintAreas:=False, _
                            OpenAfterPublish:=False
  End With
Next rngDV_Value

Set rngDVListSource = Nothing
Set rngDVCell = Nothing

End Sub

If you plan to run the code multiple times you should alter the code and either add a number (or a date time stamp) or check if the output file already exists and delete it prior to exporting a new one.

Ciao,
Holger

 

[Angel_Code Help]

Thank you so much for your response. However, something is just off.. I tried to run this code on a validation list of 41 values and it's only exporting 13 of them. I'm not quite sure why or how the code is picking items from the list.

 

[HaHoBe]

Hi,

on my sample I used a list with 55 entries and could not find any problems (I added a date time stamp to the filename and ran the code twice), sample looks like

MrE_1612314 1219556 _Sample.xlsm
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P
1																temp 1
2																temp 2
3																temp 3
4							temp 55									temp 4
5																temp 5
6	text temp 55	text temp 55	text temp 55	text temp 55	text temp 55	text temp 55	text temp 55									temp 6
7	text temp 55	text temp 55	text temp 55	text temp 55	text temp 55	text temp 55	text temp 55									temp 7
8	text temp 55	text temp 55	text temp 55	text temp 55	text temp 55	text temp 55	text temp 55									temp 8
9	text temp 55	text temp 55	text temp 55	text temp 55	text temp 55	text temp 55	text temp 55									temp 9
10	text temp 55	text temp 55	text temp 55	text temp 55	text temp 55	text temp 55	text temp 55									temp 10
11	text temp 55	text temp 55	text temp 55	text temp 55	text temp 55	text temp 55	text temp 55									temp 11
12	text temp 55	text temp 55	text temp 55	text temp 55	text temp 55	text temp 55	text temp 55									temp 12
13	text temp 55	text temp 55	text temp 55	text temp 55	text temp 55	text temp 55	text temp 55									temp 13
14	text temp 55	text temp 55	text temp 55	text temp 55	text temp 55	text temp 55	text temp 55									temp 14
15																temp 15
16																temp 16
17																temp 17
18																temp 18
19																temp 19
20																temp 20
21																temp 21
22																temp 22
23																temp 23
24																temp 24
25																temp 25
26																temp 26
27																temp 27
28																temp 28
29																temp 29
30																temp 30
31																temp 31
32																temp 32
33																temp 33
34																temp 34
35																temp 35
36																temp 36
37																temp 37
38																temp 38
39																temp 39
40																temp 40
41																temp 41
42																temp 42
43																temp 43
44																temp 44
45																temp 45
46																temp 46
47																temp 47
48																temp 48
49																temp 49
50																temp 50
51																temp 51
52																temp 52
53																temp 53
54																temp 54
55																temp 55
Summary_Page

Cell Formulas
Range	Formula
A6:G14	A6	= "text " & $G$4
P1:P55	P1	="temp " & ROW()

Cells with Data Validation
Cell	Allow	Criteria
G4	List	=myDVList

f you want to you can download the sample MrE_1612314 1219556 _Sample.xlsm

Ciao,
Holger

 

[icytuvi]

Hi,

on my sample I used a list with 55 entries and could not find any problems (I added a date time stamp to the filename and ran the code twice), sample looks like

MrE_1612314 1219556 _Sample.xlsm
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P
1																temp 1
2																temp 2
3																temp 3
4							temp 55									temp 4
5																temp 5
6	text temp 55	text temp 55	text temp 55	text temp 55	text temp 55	text temp 55	text temp 55									temp 6
7	text temp 55	text temp 55	text temp 55	text temp 55	text temp 55	text temp 55	text temp 55									temp 7
8	text temp 55	text temp 55	text temp 55	text temp 55	text temp 55	text temp 55	text temp 55									temp 8
9	text temp 55	text temp 55	text temp 55	text temp 55	text temp 55	text temp 55	text temp 55									temp 9
10	text temp 55	text temp 55	text temp 55	text temp 55	text temp 55	text temp 55	text temp 55									temp 10
11	text temp 55	text temp 55	text temp 55	text temp 55	text temp 55	text temp 55	text temp 55									temp 11
12	text temp 55	text temp 55	text temp 55	text temp 55	text temp 55	text temp 55	text temp 55									temp 12
13	text temp 55	text temp 55	text temp 55	text temp 55	text temp 55	text temp 55	text temp 55									temp 13
14	text temp 55	text temp 55	text temp 55	text temp 55	text temp 55	text temp 55	text temp 55									temp 14
15																temp 15
16																temp 16
17																temp 17
18																temp 18
19																temp 19
20																temp 20
21																temp 21
22																temp 22
23																temp 23
24																temp 24
25																temp 25
26																temp 26
27																temp 27
28																temp 28
29																temp 29
30																temp 30
31																temp 31
32																temp 32
33																temp 33
34																temp 34
35																temp 35
36																temp 36
37																temp 37
38																temp 38
39																temp 39
40																temp 40
41																temp 41
42																temp 42
43																temp 43
44																temp 44
45																temp 45
46																temp 46
47																temp 47
48																temp 48
49																temp 49
50																temp 50
51																temp 51
52																temp 52
53																temp 53
54																temp 54
55																temp 55
Summary_Page

Cell Formulas
Range	Formula
A6:G14	A6	= "text " & $G$4
P1:P55	P1	="temp " & ROW()

Cells with Data Validation
Cell	Allow	Criteria
G4	List	=myDVList

f you want to you can download the sample MrE_1612314 1219556 _Sample.xlsm

Ciao,
Holger

Hi Holger, I'd like to download the sample as well, this is exactly what I am in need of. Could you please re-upload it? Thank you very much.

 

[HaHoBe]

Hi icytuvi,

the files hosted usually are taken down after about 3 months (quite some time for any user to have a look at the files).

Link to updated workbook: MrE_1612314_1219556 _Sample_230405.xlsm

Ciao,
Holger