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).
0
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…
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.
2
perhaps something like:
snQ.PageSetup.PrintArea = "$A$1:$K74"
snQ.PrintOut
snQ.PageSetup.PrintArea = "$A$75:$K146"
snQ.PrintOut
...
1