Distinct Print Layout for each page in a Worksheet

limetoad

New Member
Joined
Jun 10, 2013
Messages
8
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

MrExcel MVP
Joined
Apr 9, 2008
Messages
3,658
Copy the below code and paste it into ThisWorkbook module.
Macro should be allowed.
Rich (BB code):
' 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

New Member
Joined
Jun 10, 2013
Messages
8
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

MrExcel MVP
Joined
Apr 9, 2008
Messages
3,658
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
 

Forum statistics

Threads
1,082,276
Messages
5,364,195
Members
400,786
Latest member
ismi88

Some videos you may like

This Week's Hot Topics

  • populate from drop list with multiple tables
    Hi All, i have a drop list that displays data, what i want is when i select one of those from the list to populate text from different tables on...
  • Find list of words from sheet2 in sheet1 before a comma and extract text vba
    Hi Friends, Trying to find the solution on my task. But did not find suitable one to the need. Here is my query and sample file with details...
  • Dynamic Formula entry - VBA code sought
    Hello, really hope one of you experts can help with this - i've spent hours on this and getting no-where. .I have a set of data (more rows than...
  • Listbox Header
    Have a named range called "AccidentsHeader" Within my code I have: [CODE]Private Sub CommandButton1_Click() ListBox1.RowSource =...
  • Complex Heat Map using conditional formatting
    Good day excel world. I have a concern. Below link have a list of countries that carries each country unique data. [URL...
  • Conditional formatting
    Hi good morning, hope you can help me please, I have cells P4:P54 and if this cell is equal to 1 then i want row O to say "Fully Utilised" and to...
Top