LoopPrint PDF to LoopPrint to Printer

GRL87

New Member
Joined
Oct 6, 2020
Messages
2
Office Version
  1. 365
  2. 2016
Platform
  1. Windows
Hi

I'm fairly basic with VBA and someone very kindly wrote the following code that would export all reports from a drop down list into single PDF's which is great but I need to change it so that it prints to a physical printer instead. Can anyone tell me what I would change for it to go to a printer? I have tried to use print out, print all and I only get the current data that I have selected, I can't get the loop to work. Many thanks.

Sub LoopPrintPDF()

response = MsgBox("Are you sure you want to print EVERY student to PDF?", vbYesNo)

If response = vbNo Then
MsgBox ("Printing Cancelled")
Exit Sub
End If

Sheets("Input page").Select
Range("D9").Select
Do
Selection.Copy
Sheets("Individual PLCs").Select
Range("A3").Select
ActiveSheet.Paste
Application.CutCopyMode = False
'ActiveWindow.SelectedSheets.PrintOut Copies:=1, Collate:=True

Dim sFileName As String
Const MyFolder As String = "P:\staff\PLC 2020\Y11 PDF Bulk Print\"

With Sheets("Individual PLCs")
sFileName = Range("A4").Value

ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, _
Filename:=MyFolder & sFileName, _
Quality:=xlQualityStandard, _
IncludeDocProperties:=True, IgnorePrintAreas:=False, OpenAfterPublish:=False
End With

Sheets("Input page").Select

ActiveCell.Offset(1, 0).Select

'Loop Until IsEmpty(ActiveCell.Offset(0, 1))
Loop Until (ActiveCell.Offset(0, 0) = "")
Sheets("Individual PLCs").Select
End Sub
 

Some videos you may like

Excel Facts

Difference between two dates
Secret function! Use =DATEDIF(A2,B2,"Y")&" years"&=DATEDIF(A2,B2,"YM")&" months"&=DATEDIF(A2,B2,"MD")&" days"

Nothnless

Board Regular
Joined
Apr 28, 2016
Messages
134
Hi,

I'm no pro like these other guys but try this real quick to see if it works:

Sub LoopPrintPDF()

response = MsgBox("Are you sure you want to print EVERY student to PDF?", vbYesNo)

If response = vbNo Then
MsgBox ("Printing Cancelled")
Exit Sub
End If

Sheets("Input page").Select
Range("D9").Select
Do
Selection.Copy
Sheets("Individual PLCs").Select
Range("A3").Select
ActiveSheet.Paste
Application.CutCopyMode = False
ActiveWindow.SelectedSheets.PrintOut Copies:=1, Collate:=True

Sheets("Input page").Select

ActiveCell.Offset(1, 0).Select

'Loop Until IsEmpty(ActiveCell.Offset(0, 1))
Loop Until (ActiveCell.Offset(0, 0) = "")
Sheets("Individual PLCs").Select
End Sub
 

GRL87

New Member
Joined
Oct 6, 2020
Messages
2
Office Version
  1. 365
  2. 2016
Platform
  1. Windows
This works! Thank you so much - I spent many hours trying to delete bits to make it work. It is definitely something that I need to develop!

Thanks
 

Watch MrExcel Video

Forum statistics

Threads
1,119,240
Messages
5,576,903
Members
412,753
Latest member
Coach_Olson
Top