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

What did Pito Salas invent?
Pito Salas, working for Lotus, popularized what would become to be pivot tables. It was released as Lotus Improv in 1989.

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,118,765
Messages
5,574,116
Members
412,572
Latest member
Chriszion
Top