Distinct Print Layout for each page in a Worksheet

[limetoad]

Not sure if this is possible, but figured I'd check here just in case. I have a sheet that has 3 different pages on the same tab, and I'd like to be able to set the print layout/setup of each page in the sheet separately. More specifically, I want to print the first two pages at PageSetup.Zoom = 50 and the last page at PageSetup.Zoom = 100. Anyone know how I could do that? otherwise I have to put the 3rd page on a separate tab, and I have s many tabs that that will makes things even crazier. Any ideas?

 

[ZVI]

Copy the below code and paste it into ThisWorkbook module.
Macro should be allowed.

' Put this code into Thisworkbook module
Private Sub Workbook_BeforePrint(Cancel As Boolean)
  Const MySheet = "Sheet1"  ' <-- Change to suit
  If Not ActiveSheet Is Sheets(MySheet) Then Exit Sub
  Application.EnableEvents = False
  With ActiveSheet
    .PageSetup.Zoom = 50
    .PrintOut 1, 2
    .PageSetup.Zoom = 100
    .PrintOut 3
    .PageSetup.Zoom = 50
  End With
  Cancel = True
  Application.EnableEvents = True
End Sub

 

[limetoad]

Thanks! That works almost perfectly... The only problem is that when I print to a PDF it creates 2 separate PDF files. Is there a way to combine the print jobs into one so that it prints out to one PDF?

 

[ZVI]

Hi,

If PDF printing has been mentioned in the start post then I would not participate in this thread
Because PDF printer can’t provide suspending and feedback to VBA during printing for changing the PageSetup.zoom in Excel with farther resuming.

I think it's not suitable, but at least you can use Acrobat for manual joining PDFs into the single file.
Or try googling for free alternatives like pdfbinder (I am not familiar with any of them).
If you'll find command line utility then theoretically joining can be maid via VBA and Shell function.

Regards

 

[limetoad]

Ok, thanks for the feedback!