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

Waterfall charts in Excel?
Office 365 customers have access to Waterfall charts since late 2016. They were added to Excel 2019.

Nothnless

Board Regular
Joined
Apr 28, 2016
Messages
130
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,114,002
Messages
5,545,440
Members
410,684
Latest member
LakTik
Top