CODEWITHSUNDEEP
excelvbapage-break
user1135218
user1135218

Reputation: 403

Set page layout to produce a three page pdf

I have a macro which creates PDFs from a sheet.
Depending on the laptop of the user, the page breaks and size is different.
I read that is due to the screen resolution/Windows zoom, which seems to be the problem, as each user has different scale % in their Display settings in Windows.
I am trying to set it in VBA.

With snQ.PageSetup
    .Zoom = False
    .FitToPagesTall = 1
    .FitToPagesWide = 3
End With
snQ.ResetAllPageBreaks
Set snQ.HPageBreaks(2).Location = snQ.Range("B74")
Set snQ.HPageBreaks(2).Location = snQ.Range("B146")

This sets the printing area to three pages.
However the page breaks between the three pages don't change (or change to wrong place) depending on each person.
Seems when I apply the pagebreaks, the page layout changes from 1-3 pages to a %, as if I go to the page setting, is no longer 1-3, but zoom 83% (in my case).

Upvotes: 0

Views: 80

Answers (3)

kirk
kirk

Reputation: 1

perhaps something like:

snQ.PageSetup.PrintArea = "$A$1:$K74"

snQ.PrintOut

snQ.PageSetup.PrintArea = "$A$75:$K146"

snQ.PrintOut

...

Upvotes: -1

user1135218
user1135218

Reputation: 403

At the end I opted for separating each page (each in its own sheet) then when creating pdf selecting all 3 before pdf is created. This creates 3 continuous sheets in one pdf. Each sheet is marked as 1 page width, 1 page height. This method seems to work for all users, regardless of its windows zoom...

Upvotes: 1

TinMan
TinMan

Reputation: 7759

PageSetup.Zoom property Returns or sets a Variant value that represents a percentage (between 10 and 400 percent) by which Microsoft Excel will scale the worksheet for printing.

snQ.PageSetup.Zoom = False

False evaluates as 0 which is invalid. You could try 100 which is 100% (normal view).

snQ.PageSetup.Zoom = 100

This removes all Page Breaks

snQ.ResetAllPageBreaks

With no programmatic page breaks snQ.HPageBreaks.Count = 0

I guess the OP is using On Error Resume Next because this throws an error:

Set snQ.HPageBreaks(2).Location = snQ.Range("B74")

Here is the proper way to add page breaks:

snQ.HPageBreaks.Add Before:=snQ.Range("B74")
snQ.HPageBreaks.Add Before:=snQ.Range("B146")

But this is adding horizontal page breaks and the code is setting the print area to 3 pages wide.FitToPagesWide = 3 and only one page tall .FitToPagesTall = 1. Use VPageBreaks for vertical page breaks.

If can't figure it out, consider appending each page to a pdf. Let me know if you need clarification on this.

Upvotes: 0

Related Questions