cycle through data validation list to print to pdf and save as cell text

[daikeda]

Hi,
I have:
- worksheet name("test1")
- data validation list in "A"
- print range is first page of worksheet or A1: I45

looking if there's a way to have macro that cycles through the data validation list and prints a PDF with the name of the text in that cell and loop through so every item of list prints as their own pdf. aka if there's 100 lines in the drop down, then i'd end with 100 pdfs each individually named for the 100 drop down options.

 

[John_w]

Which is the data validation dropdown cell? This macro assumes B2.

Public Sub Create_PDFs()

    Dim destinationFolder As String
    Dim dataValidationCell As Range, dataValidationListSource As Range, dvValueCell As Range
    
    destinationFolder = ThisWorkbook.Path     'Same folder as workbook containing this macro
    '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("test1").Range("B2")
     
    '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.Range("A1:I45")
            .ExportAsFixedFormat Type:=xlTypePDF, Filename:=destinationFolder & dvValueCell.Value & ".pdf", _
                Quality:=xlQualityStandard, IncludeDocProperties:=True, IgnorePrintAreas:=False, OpenAfterPublish:=False
        End With
    Next
        
End Sub

 

[rayzuki]

Which is the data validation dropdown cell? This macro assumes B2.

Public Sub Create_PDFs()

    Dim destinationFolder As String
    Dim dataValidationCell As Range, dataValidationListSource As Range, dvValueCell As Range
   
    destinationFolder = ThisWorkbook.Path     'Same folder as workbook containing this macro
    '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("test1").Range("B2")
    
    '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.Range("A1:I45")
            .ExportAsFixedFormat Type:=xlTypePDF, Filename:=destinationFolder & dvValueCell.Value & ".pdf", _
                Quality:=xlQualityStandard, IncludeDocProperties:=True, IgnorePrintAreas:=False, OpenAfterPublish:=False
        End With
    Next
       
End Sub

Hello sir.. this code work in my worksheet, i really apreciate it and really thanks you, this code make my work better faster than before.
and can i ask about how to make remove blank page in this code.

 

[John_w]

how to make remove blank page in this code.

There shouldn't be any blank pages if you're using exactly the same code and the width of columns A:I (range A1:I45) is 1 page wide.

It's best if you start a new thread, posting your code and worksheet data (using XL2BB - Excel Range to BBCode) and describe exactly where the blank page is occurring.

 

[Carla carla]

Which is the data validation dropdown cell? This macro assumes B2.

Public Sub Create_PDFs()

    Dim destinationFolder As String
    Dim dataValidationCell As Range, dataValidationListSource As Range, dvValueCell As Range
   
    destinationFolder = ThisWorkbook.Path     'Same folder as workbook containing this macro
    '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("test1").Range("B2")
    
    '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.Range("A1:I45")
            .ExportAsFixedFormat Type:=xlTypePDF, Filename:=destinationFolder & dvValueCell.Value & ".pdf", _
                Quality:=xlQualityStandard, IncludeDocProperties:=True, IgnorePrintAreas:=False, OpenAfterPublish:=False
        End With
    Next
       
End Sub

 

[Carla carla]

Hello, I have been using this code and its working perfectly until now. I am getting this message but I do not know how to resolve. Please can you help.

 

[Joe4]

That seems to suggest that maybe the code you posted above is not located within a procedure.
It must appear in a procedure, or a function.

All procedures begin with a line like:

Sub MyProcedureName()

or

Private Sub MyProcedureName()

where "MyProcedureName" is whatever name you decide to give it.
And they all end with this line:

End Sub

And all functions start with a line something like:

Function MyFunctionName(...)

and end with

End Function

 

[Carla carla]

That seems to suggest that maybe the code you posted above is not located within a procedure.
It must appear in a procedure, or a function.

All procedures begin with a line like:

Sub MyProcedureName()

or

Private Sub MyProcedureName()

where "MyProcedureName" is whatever name you decide to give it.
And they all end with this line:

End Sub

And all functions start with a line something like:

Function MyFunctionName(...)

and end with

End Function

 

[Carla carla]

Hello,
This is the code I am trying to use, I cant figure out what's wrong

Dim destinationFolder As String
Dim dataValidationCell As Range, dataValidationListSource As Range, dvValueCell As Range

destinationFolder = ThisWorkbook.Path 'Same folder as workbook containing this macro
'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("").Range("N8")

'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.Range("A1:I45")
.ExportAsFixedFormat Type:=xlTypePDF, Filename:=destinationFolder & dvValueCell.Value & ".pdf", _
Quality:=xlQualityStandard, IncludeDocProperties:=True, IgnorePrintAreas:=False, OpenAfterPublish:=False
End With
Next

End Sub

 

[Joe4]

Looks like you just quoted my post, but didn't add anything new to it.

Hello,
This is the code I am trying to use, I cant figure out what's wrong

Dim destinationFolder As String
Dim dataValidationCell As Range, dataValidationListSource As Range, dvValueCell As Range

destinationFolder = ThisWorkbook.Path 'Same folder as workbook containing this macro
'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("").Range("N8")

'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.Range("A1:I45")
.ExportAsFixedFormat Type:=xlTypePDF, Filename:=destinationFolder & dvValueCell.Value & ".pdf", _
Quality:=xlQualityStandard, IncludeDocProperties:=True, IgnorePrintAreas:=False, OpenAfterPublish:=False
End With
Next

End Sub

As I said, you are missing the procedure declaration line, that begins with "Sub..." or "Private Sub"!

If you look at the code in post #2, you can see it at the very top:

Sub Create_PDFs()

I recommend you read some articles on how to write/create VBA code to gain a better understanding of the requirements, like this one here:

What is a VBA Procedure, Sub Procedure, Function Procedure?

What is a VBA procedure, how to declare a VBA procedure, how to name a VBA procedure, what to type inside a VBA procedure?

www.emagenit.com