#1
|
|||
|
|||
Macro to print to pdf files showing pupil names
Hi
I have a spreadsheet that has a macro that when clicked it prints a single page to a pdf and names it to a number. I would like this to name it as a student name not a number and send to the printer at the same time. Printing 15 pdf's at a time is very time consuming! TIA |
#2
|
|||
|
|||
Care to show us your current macro ?
|
#3
|
|||
|
|||
Current macro
Code:
Sub CombinedScienceExport() Dim FolderName As String, fName As String Dim inputRange As Range, r As Range, c As Range Application.ScreenUpdating = False '''' Open file dialog and choose folder With Application.FileDialog(msoFileDialogFolderPicker) .AllowMultiSelect = False If .Show = True Then FolderName = .SelectedItems(1) & "" Else Exit Sub End If End With '''' Location of DataValidation cell Set r = Worksheets("Student Report Combined Science").Range("S2") '''' Get DataValidation values Set inputRange = Evaluate(r.Validation.Formula1) '''' Loop through DataValidation list For Each c In inputRange r.Value = c.Value fName = c.Value '''' Save as pdf ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, Filename:=FolderName & fName, _ Quality:=xlQualityStandard, IncludeDocProperties:=True, IgnorePrintAreas:=False, OpenAfterPublish:=False Next c Application.ScreenUpdating = True End Sub Last edited by macropod; 12-14-2020 at 02:53 PM. Reason: Added code tags |
#4
|
|||
|
|||
Sending to the printer should be easy enough - just add in activesheet.printout after the activesheet.exportasfixedformat. but this will print our each one every time - might be better to wrap an if around it to ask if the user wants to print
Code:
For Each c In inputRange r.Value = c.Value fName = c.Value '''' Save as pdf ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, Filename:=FolderName & fName, _ Quality:=xlQualityStandard, IncludeDocProperties:=True, IgnorePrintAreas:=False, OpenAfterPublish:=False ActiveSheet.PrintOut Next c |
#5
|
|||
|
|||
file attached
Hi. Master attached,
VBA is above my skills! so any help is very appreciated. I was sent this spreadsheet! Thankyou |
#6
|
|||
|
|||
it looks like it is using UPN (what is that?), what name do you want the PDF saved as - just first name? I cant see a surname
|
#7
|
|||
|
|||
UPN is a Unique pupil number. the "Nameformat1" is the full pupil name, this is what i'd like them to be called please.
Thanks |
#8
|
|||
|
|||
Try this - it errors after the 2 populated rows but i assume it will only run for the number of children in the list
|
#9
|
|||
|
|||
adjustments
Quote:
I tried this and there was a formula error in the main table. and I couldn't get it to work. I have attached again with a line of data in to show how it works. Richard |
#10
|
|||
|
|||
Okay, so i have added a question - do you want to print yes/no. Yes will print all to PDF AND paper copies, No will just do the PDFs
I have then changed the file name to be the name - might be worth having the UPN as well in the file name in case you have names that are the same? also worth adding the year so next years wont over write the previous? - Monkhouse, Bob - UPN 123456789 - year 8 2020.pdf - only a suggestion |
#11
|
|||
|
|||
Quote:
Yes i agree that would be wise to change! Thankyou so much for this. Works a treat and saves me a lot of time! Much Appreciated |
#12
|
|||
|
|||
Didnt know if you knew how to update the file name
Also forgot to say that the macro will exit when it see a 0 in the UPN column (no more children) |
#13
|
|||
|
|||
Quote:
Yeah i don't know how to do that! |
#14
|
|||
|
|||
Did you see that i did it on the last upload?
|
Thread Tools | |
Display Modes | |
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
Footers not showing in print preview nor will they print | Lee_Lee | Word | 3 | 09-19-2017 05:06 AM |
How do I import list of names from excel to print placecards? | jrawls | Word | 2 | 08-17-2017 06:53 AM |
Why 'print forms data' print pictures for DOCX files | koolprasad2003 | Word | 4 | 05-09-2014 03:26 PM |
VBA: How to debug.Print enumeration names? | tinfanide | Word VBA | 4 | 01-27-2012 01:41 AM |