Macro to cycle through drop-down selection to print to pdf (with loop?)

[jbeanx80]

Hello,

I've got an excel workbook. There's a drop-down menu (validated) on the main worksheet I'd like to print to PDF. It populates data based on the drop-down option and the data is pulled from other worksheets. I'd like to

1) select from the drop down menu,
2) print the main spreadsheet to PDF, save it as "PPK 2.71, the name as it appears on the dropdown, and date (for example PPK 2.71-1-200226)
3) select the next item in the drop down menu
4) print to pdf (for example PPK 2.71-2-200226)
4) select the next item until it reaches the last item (# of items could vary from one workbook to another)

The print range of the main worksheet is already set to print one 1 page.

 

[jbeanx80]

Sorry, I forgot to mention that I've searched and found a lot of similar vba coding, but I still don't know how to customize it to fit my particular needs.
Thank you all in advance for helping.

 

[Silverhorne]

I think this code will give you what you want, I am unsure if it works with Excel 2013 or not. I have never used VBA before although I needed a similar solution and it works for me.
I stole solutions from a few other post answers and combined them, I've received a lot of help from here and would like to try to give back.
Feel free to recommend a cleaner solution if anyone has one.

I have sometimes +1000 reports to print out at the end of a job, the report tab pulls data from other worksheets based on the value entered into cell "L7" of the report sheet.
"L7" has the drop down data validation with a list of the unique items. When you run the macro, it will cycle through the drop down list and print each file with the value in "L7".
I have it set up to store these files in the same folder as the excel file you are printing from [Filename:=ThisWorkbook.Path & "\"].
I needed a prefix for each file name so I added [& "1-6QCV - " ] to the file name, you can search the forum for how to add the current date, etc..
You will need to change the "L7" in 3 locations to the cell containing your data validation dropdown.
I added a button to my sheet and tagged the macro to it, it prints reports faster than I can count them.
Hope this helps.

Sub Print_All_To_PDF()
Dim strValidationRange As String
Dim rngValidation As Range
Dim rngDepartment As Range

' Turn off screen updating
Application.ScreenUpdating = False

' Identify the source list of the data validation
strValidationRange = Range("L7").Validation.Formula1
Set rngValidation = Range(strValidationRange)

' Set the value in the selection cell to each selection in turn
' and print the results.
For Each rngDepartment In rngValidation.Cells
Range("L7").Value = rngDepartment.Value
ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, Filename:=ThisWorkbook.Path & "\" & "1-6QCV - " & Range("L7").Value _
    , Quality:=xlQualityStandard, IncludeDocProperties:=True, IgnorePrintAreas _
    :=False, OpenAfterPublish:=False
Next
' Turn screen updating back on
Application.ScreenUpdating = True
End Sub

 

[ynyend1]

I think this code will give you what you want, I am unsure if it works with Excel 2013 or not. I have never used VBA before although I needed a similar solution and it works for me.
I stole solutions from a few other post answers and combined them, I've received a lot of help from here and would like to try to give back.
Feel free to recommend a cleaner solution if anyone has one.

I have sometimes +1000 reports to print out at the end of a job, the report tab pulls data from other worksheets based on the value entered into cell "L7" of the report sheet.
"L7" has the drop down data validation with a list of the unique items. When you run the macro, it will cycle through the drop down list and print each file with the value in "L7".
I have it set up to store these files in the same folder as the excel file you are printing from [Filename:=ThisWorkbook.Path & "\"].
I needed a prefix for each file name so I added [& "1-6QCV - " ] to the file name, you can search the forum for how to add the current date, etc..
You will need to change the "L7" in 3 locations to the cell containing your data validation dropdown.
I added a button to my sheet and tagged the macro to it, it prints reports faster than I can count them.
Hope this helps.

Sub Print_All_To_PDF()
Dim strValidationRange As String
Dim rngValidation As Range
Dim rngDepartment As Range

' Turn off screen updating
Application.ScreenUpdating = False

' Identify the source list of the data validation
strValidationRange = Range("L7").Validation.Formula1
Set rngValidation = Range(strValidationRange)

' Set the value in the selection cell to each selection in turn
' and print the results.
For Each rngDepartment In rngValidation.Cells
Range("L7").Value = rngDepartment.Value
ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, Filename:=ThisWorkbook.Path & "\" & "1-6QCV - " & Range("L7").Value _
    , Quality:=xlQualityStandard, IncludeDocProperties:=True, IgnorePrintAreas _
    :=False, OpenAfterPublish:=False
Next
' Turn screen updating back on
Application.ScreenUpdating = True
End Sub

When I run this code in my sheet after changing the L7 reference it brings a Run-time error '1004' Application-defined or object-defined error... It brings this error once I step in after the third time F8 pressing... What's wrong with this code on my side??