![]() |
|
#1
|
|||
|
|||
|
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
|
|||
|
|||
|
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
|
|||
|
|||
|
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
|
|||
|
|||
|
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?
|
|
|
|
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 |