How to automate save / print from drop down list vba dynamically updated data continued

[MikeBa41]

Hi All,
I am a new member and saw a post with the above title that cam close to solving my problem but I still need help if possible.
I am using Excel 2016 on the iMac for this project (not by choice).
I have an excel Pivot table on a tab named"Summary" which has a drop down in cell "F2" which points to a another sheet called "Drop Down" containing 100 client names and allows choosing a client's name from and updates the individual client's information on the "Summary" sheet which then prepares/allows the choosing of save as PDF and a dialog box pops up asking for the names of the file to save and where (the file name is the same name that displays in the dropdown in cell "F2" and once filled in it saves the PDF to the documents folder with the Name of Client Statements 8/15/15 or 8/31/15 these statements are prepared twice a month.
I believe LukeM's code will work but I don't fully understand it, like the &#39, I have listed his code below; any help will be greatly appreciated thank you MikeBa41:

Sub PsuedoCode()
Dim xName AsString
Dim c As Range
Dim MyList As Range
Dim DropRange As Range

'Where is list of names?
Set MyList = Worksheets("Sheet1").Range("A1:A100")
'Where is dropdown?
Set DropRange = Worksheets("Sheet1").Range("b1")

Application.ScreenUpdating = False
ForEach c In MyList
xName = c.Value
DropRange.Value = xName

'workbook should automatically calculate/run macro
'at this point

'TODO:
'Record a macro of you printing to a pdf
'should look something like:
Application.ActivePrinter = "Adobe PDF on Ne02:"
ExecuteExcel4Macro "PRINT(1,,,1,,,,,,,,2,""Adobe PDF on Ne02:"",,TRUE,,FALSE)"
'or, if you see where it record the file name, you can use the
'xName variable to populate the name
Next
Application.ScreenUpdating = True
EndSub

 

[Luke M]

Hi Mike, and welcome to the forum!

Looks like you stumbled across an older post of mine. The Chandoo site did a forum migration a couple years ago, and unfortunately several code characters changed into their ASCII code. E.g., the ' mark changed into '
Not very helpful I know. Us moderators have been trying to fix them as we come across, but it still causes confusion occasionally.

Here's the above macro cleaned up:

Sub PsuedoCode()
Dim xName As String
Dim c As Range
Dim MyList As Range
Dim DropRange As Range

'Where is list of names?
Set MyList = Worksheets("Sheet1").Range("A1:A100")
'Where is dropdown?
Set DropRange = Worksheets("Sheet1").Range("b1")

Application.ScreenUpdating = False
For Each c In MyList
    xName = c.Value
    DropRange.Value = xName
  
    'workbook should automatically calculate/run macro
    'at this point
  
    'TODO:
    'Record a macro of you printing to a pdf
    'should look something like:
    Application.ActivePrinter = "Adobe PDF on Ne02:"
    ExecuteExcel4Macro "PRINT(1,,,1,,,,,,,,2,""Adobe PDF on Ne02:"",,TRUE,,FALSE)"
    'or, if you see where it record the file name, you can use the
    'xName variable to populate the name
Next
Application.ScreenUpdating = True
End Sub

Does that help, or do you still need assistance with your file?

 

[MikeBa41]

Hi Mike, and welcome to the forum!

Looks like you stumbled across an older post of mine. The Chandoo site did a forum migration a couple years ago, and unfortunately several code characters changed into their ASCII code. E.g., the ' mark changed into '
Not very helpful I know. Us moderators have been trying to fix them as we come across, but it still causes confusion occasionally.

Here's the above macro cleaned up:

Sub PsuedoCode()
Dim xName As String
Dim c As Range
Dim MyList As Range
Dim DropRange As Range

'Where is list of names?
Set MyList = Worksheets("Drop Down").Range("A1:A111")
'Where is dropdown?
Set DropRange = Worksheets("Summary").Range("F2")

Application.ScreenUpdating = False
For Each c In MyList
    xName = c.Value
    DropRange.Value = xName
  
    'workbook should automatically calculate/run macro
    'at this point
  
    'TODO:
    'Record a macro of you printing to a pdf
    'should look something like:
    Application.SaveAs = "Adobe PDF"
    ExecuteExcel4Macro "PRINT(1,,,1,,,,,,,,2,""Adobe PDF"",,TRUE,,FALSE)"
    'or, if you see where it record the file name, you can use the
    'xName variable to populate the name
Next
Application.ScreenUpdating = True
EndSub

Does that help, or do you still need assistance with your file?

HI Luke,
Thank you for your reply and cleaning up the code, I still need some help. I am trying to run in iMac excel 2016 (is it not friendly at all) I have changed the code to accommodate the iMac, not sure if I have got it correct. I basically am trying to save as PDF 110 different client statements to a folder and then email them once I check for accuracy. I appreciate any help you can give.
Thank you,
MikeBa41

 

[Luke M]

Sure thing Mike.

If you look at the code, you can see that you need to define where the Data Validation (dropdown) list is located. Then, the code is going to loop through all the names/cells in that list. Sounds like your workbook then will update/change the summary sheet as needed.

All that's left then is the printout. If you can, record a short macro of you printing something to PDF. You might be able to use this (adjusting first 3 lines as needed), but I'm not sure about Excel 2016 compatibility.

Sub PsuedoCode()
Dim xName As String
Dim c As Range
Dim MyList As Range
Dim DropRange As Range
Dim wsPrint As Worksheet

'Where is list of names?
Set MyList = Worksheets("Sheet1").Range("A1:A100")
'Where is dropdown?
Set DropRange = Worksheets("Sheet1").Range("b1")
'Which sheet to print?
Set wsPrint = Worksheets("Summary")

Application.ScreenUpdating = False
For Each c In MyList
    xName = c.Value
    DropRange.Value = xName
   
    'Not sure if this line will work in Excel 2016. May need to record a macro of you
    'print a PDF to seek alternate coding
    wsPrint.ExportAsFixedFormat xlTypePDF, "Summary for " & xName & ".pdf", , , , , , False
Next
Application.ScreenUpdating = True
End Sub

 

[QTip]

Hi, got 2 questions regarding this tread;

1. The code above prints out individual documents, is there a way to print all items from the drop down menu in one document?

2. I got 2 drop down menus, can I write a macro to print a specific groupings from the two drop down menu?

Thanks heaps!!!

 

[Jmcswain]

I have the same needs as QTIP's #1 but I would also like to interject an other piece of code into the process that auto-adjusts my row heights. I have that code and working it into this would be incredibly helpful.