Changing print set settings on different pages

[alchaz001]

Hi everybody I hope you're all keeping well.

I've got this code to enable me to select the pages that i want to print using a dialog box:


Sub PrintVoucher1_Click()


' Display "Printer Setup" dialog box
Application.Dialogs(xlDialogPrinterSetup).Show
' Option Explicit
' Sub SelectSheets()
Dim i As Integer
Dim TopPos As Integer
Dim SheetCount As Integer
Dim PrintDlg As DialogSheet
Dim CurrentSheet As Worksheet
Dim cb As CheckBox
Dim Numcop As Long
Application.ScreenUpdating = False
' Check for protected workbook
If ActiveWorkbook.ProtectStructure Then
MsgBox "Workbook is protected.", vbCritical
Exit Sub
End If
' Add a temporary dialog sheet
Set CurrentSheet = ActiveSheet
x = CurrentSheet.Name
Set PrintDlg = ActiveWorkbook.DialogSheets.Add
SheetCount = 0
' Add the checkboxes
TopPos = 40
For i = 1 To ActiveWorkbook.Worksheets.Count
Set CurrentSheet = ActiveWorkbook.Worksheets(i)
' Skip empty sheets and hidden sheets
If Application.CountA(CurrentSheet.Cells) <> 0 And _
CurrentSheet.Visible Then
SheetCount = SheetCount + 1
PrintDlg.CheckBoxes.Add 78, TopPos, 150, 16.5
PrintDlg.CheckBoxes(SheetCount).Text = _
CurrentSheet.Name
TopPos = TopPos + 13
End If
Next i
' Move the OK and Cancel buttons
PrintDlg.Buttons.Left = 240
' Set dialog height, width, and caption
With PrintDlg.DialogFrame
.Height = Application.Max _
(68, PrintDlg.DialogFrame.Top + TopPos - 34)
.Width = 230
.Caption = "Select sheets to print"
End With
' Change tab order of OK and Cancel buttons
' so the 1st option button will have the focus
PrintDlg.Buttons("Button 2").BringToFront
PrintDlg.Buttons("Button 3").BringToFront
' Get the number of print copies for each report
Numcop = Application.InputBox("Enter number of copies to print:", _
"How Many Copies?", 1, Type:=1)
If Numcop = 0 Then
ElseIf Len(Numcop) > 0 Then
End If
' Display the dialog box
CurrentSheet.Activate
Dim cnt As Integer
Application.ScreenUpdating = True
If SheetCount <> 0 Then
If PrintDlg.Show Then
For Each cb In PrintDlg.CheckBoxes
If cb.Value = xlOn Then
If cnt = 0 Then
Worksheets(cb.Caption).Select ' Replace:=False 'Activate
Else
Worksheets(cb.Caption).Select Replace:=False 'Activate
End If
cnt = cnt + 1
End If
Next cb
ActiveWindow.SelectedSheets.PrintOut copies:=Numcop
'ActiveSheet.PrintPreview 'for debugging
End If
Else
MsgBox "All worksheets are empty."
End If
' Delete temporary dialog sheet (without a warning)
Application.DisplayAlerts = False
PrintDlg.Delete
' Reactivate original sheet
Sheets(x).Select
End Sub

(A big thank you to the author of this code by the way!)


At the moment, when I print, it treats all pages the same; that's to say all pages are printed in DL (220mm x 110mm) size. Is it possible to modify this code to set the print setting for each page individually?


So that worksheet 1 prints a DL size (for a voucher) page.

So that worksheet 2 prints a A4 size (for a welcome letter) page.

So that worksheet 3 prints a A5 size (for a cover note) page.

thanks in advance guys.

Alchaz001

 

[pbornemeier]

This code will set the paper size for each worksheet.

    Worksheets("Sheet1").PageSetup.PaperSize = xlPaperEnvelopeDL
    Worksheets("Sheet2").PageSetup.PaperSize = xlPaperA4
    Worksheets("Sheet3").PageSetup.PaperSize = xlPaperA5

You may want to consider using the Page Setup Excel4 Macro to establish the print settings for each worksheet. It is much faster, especially if you are printing to a network printer. This post discusses it:

http://www.mrexcel.com/forum/showthread.php?t=362900

 

[alchaz001]

This code will set the paper size for each worksheet.

    Worksheets("Sheet1").PageSetup.PaperSize = xlPaperEnvelopeDL
    Worksheets("Sheet2").PageSetup.PaperSize = xlPaperA4
    Worksheets("Sheet3").PageSetup.PaperSize = xlPaperA5

You may want to consider using the Page Setup Excel4 Macro to establish the print settings for each worksheet. It is much faster, especially if you are printing to a network printer. This post discusses it:

http://www.mrexcel.com/forum/showthread.php?t=362900

Thanks - will research this and tell you how I get on..