Excel VBA Course
Excel VBA Course - From Beginner to Expert

200+ Video Lessons
50+ Hours of Video
200+ Excel Guides

Become a master of VBA and Macros in Excel and learn how to automate all of your tasks in Excel with this online course. (No VBA experience required.)

View Course

Macro for printing to pdf from a data validation drop down

0

I am new to macros and need some help.

I have created a table in excel for various entities. I use a data validation drop down list to choose the entity. The tables are the same for each entity all that changes is the information based on the entity that is chosen. I want to develop a macro to print to pdf tables for each entity and save them in a folder on my desktop. Currently I have to chose from the each entity from the data validation list and then run a macro to print to pdf.

Basically I would like the macro to do the following:

1. Choose an entity from the validation list (this will populate the tables with information).
2. Print the tables to a PDF file and save to a specific folder calling the file the entity name which is in a cell underneath data validation list.
3. Repeat steps 1, 2 and 3 for every entity in the data validation list.

This is the macro that i am currently using to print to pdf. However I have to select the entity from the validation list and then run the macro each time. I would like to be able to press the print command button and it will run through the entire validation list and print each entity report as a separate pdf. 

Private Sub CommandButton1_Click()
pdfname = Range("G3").Text
ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, Filename:="\\bviapp01\data\accounts\Mgmt Accts\PDF Reports\Entity PL\" + pdfname + ".pdf" _
, IgnorePrintAreas:=False, OpenAfterPublish:=False
End Sub

Please let me know if you need any more information in order to assist. I have uploaded a sample file.

Thanks in advance for your help!! Much appreciated.

Sub drop_down_iterate() For i = 2 To 36 Sheets("Entity P&L").Range("G2").Value = Sheets("Seg1 2").Cells(i, 2).Value 'call the print macro Call macro_to_print MsgBox i Next i End Sub

I am totally new to VBA. I don't know how to sub my macro.  Also could you  please explain what call macro to print means and how I can incorporate my existing prinint macro into this so that it works.  Thanks again.

excel
posted by: pjmkay (rep: 2) on Tue Sep 13, 2016 at 1:09 pm
Post Edited
CODE Tags: You must add [CODE][/CODE] tags around your code! (click the CODE button to do this when creating a post)
Answer
Discuss

Discussion

So you already have the macro working and all you need to do is make it run once you make a selection from the drop-down menu? Because the file you included does not contain any macros.
don (rep: 1989) Sep 13, '16 at 1:51 pm
Hi Don

This is the macro that i am currently using to print to pdf. However I have to select the entity from the validation list and then run the macro each time. I would like to be able to press the print command button and it will run through the entire validation list and print each entity report as a separate pdf.
Private Sub CommandButton1_Click()
pdfname = Range("G3").Text
ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, Filename:="\\bviapp01\data\accounts\Mgmt Accts\PDF Reports\Entity PL\" + pdfname + ".pdf" _
, IgnorePrintAreas:=False, OpenAfterPublish:=False
End Sub

Thanks 
pjmkay (rep: 2) Sep 13, '16 at 2:01 pm
Please update your original Question with this information so future readers can understand the issue. 
don (rep: 1989) Sep 13, '16 at 2:06 pm
ok.  updated the question with the additional information.
pjmkay (rep: 2) Sep 13, '16 at 2:11 pm
Add to Discussion

Answers

0
Selected Answer

C'mon man, read the rules before you post! 

This macro will iterate through the data validation list that you have and "select" each item in it and then run your print macro:

Sub drop_down_iterate()

For i = 2 To 36

    Sheets("Entity P&L").Range("G2").Value = Sheets("Seg1 2").Cells(i, 2).Value

    'call the print macro
    Call macro_to_print

    MsgBox i

Next i

End Sub

You have your current "printing" code in a command button, move it to a regular Sub macro and then replace macro_to_print from my macro with the name of your macro that prints.

Update

I just meant to make your macro independent of the command button. It could look like this:

Sub macro_to_print()
pdfname = Range("G3").Text
ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, Filename:="\\bviapp01\data\accounts\Mgmt Accts\PDF Reports\Entity PL\" + pdfname + ".pdf" _
, IgnorePrintAreas:=False, OpenAfterPublish:=False
End Sub

macro_to_print is the name of the macro that contains the printing code. I put your macro printing code into its own Sub and named it that in the code above.

You could also just replace

'call the print macro

Call macro_to_print

with the code that prints the macro instead of keeping that code in a separate macro.

posted by: don (rep: 1989) on Tue Sep 13, 2016 at 2:12 pm
Discuss

Discussion

Apologies.  I added some more information to my questions.
pjmkay (rep: 2) Sep 13, '16 at 2:45 pm
Add to Discussion


Answer the Question

You must create an account to use the forum. Create an Account or Login