Select specific printer when printing from macro with VBA code

baxtrm · Feb 25th 2020

Hello!

Im fairly new to VBA coding and i'm trying to learn as much as possible, but right now i'm stuck on this issue.

I have a Excel document where i have a lot of data that transfers through to the next worksheets. I have some of them as landscaped and a few is a standing orientation.

What i'd like the end result of this would be (as close as possible i'd hope):

1) I make my selection in the listbox of the sheets i want to print

2) The macro chooses the specific printer to be printed by (this case its named "Bluebeam PDF") I need it to be this printer so that the columns will remain within the margins

3) The macro is looking for the selections i've made in the listbox

4) I get a preview of the print

5) It prints to a location i select and name the file

I have the listbox set up and a bit of the code but its the final touch that need to be done.

Is it also possible to select how many pages i will print from each worksheet?

The code that is running at the moment:

Code

Sub Print_sh()
Dim i As Long, c As Long
Dim SheetArray() As String
With ActiveSheet.ListBoxPrint
For i = 0 To .ListCount - 1
If .Selected(i) Then
ReDim Preserve SheetArray(c)
SheetArray(c) = .List(i)
c = c + 1

End If

Next i

End With

Sheets(SheetArray()).PrintPreview
Sheets(SheetArray()).PrintOut

End Sub

Display More

**royUK** · Feb 26th 2020

This code needs adapting slightly to your macro

Code

Sub printSpecificPages()
Dim NumPages As Integer
Dim StartPage As Integer, LastPage As Integer

NumPages = ActiveSheet.PageSetup.Pages.Count

StartPage = Application.InputBox("Enter a number between 1" & NumPages, "First Page")
LastPage = Application.InputBox("Enter a number between less than " & NumPages, "Last Page")

Worksheets.PrintOut from:=StartPage, to:=LastPage, copies:=1, preview:=True, collate:=True

End Sub

Display More

**royUK** · Feb 26th 2020

Is the Listbox on a userform?

**royUK** · Feb 26th 2020

This is a userform that list each sheet and the number of pages to print. There is also an option to preview before printing.

I think you might be using a ListBox on a sheet.

baxtrm · Feb 26th 2020

Hello Roy

Thank you for the replys!

I will try these tips out to see how it will do

Im attaching a printscreen of the worksheet that will make this VBA code so you can get a picture of how it looks.

**royUK** · Feb 26th 2020

An example workbook would be more useful than a screenshot

Select specific printer when printing from macro with VBA code

Participate now!

Share

Tags