Least used print area

[JackDanIce]

Hi,

Having issues with printing areas in worksheets, so have the following (partial) code:

With Activesheet.PageSetup
  .PaperSize = xlPaperA4
  .Orientation = xlPortrait
  x = (wks.HPageBreaks.Count + 1) * (wks.VPageBreaks.Count + 1)
  .Orientation = xlLandscape

  'Orientate the page for least number of page breaks
  .Orientation = IIf(x < (wks.HPageBreaks.Count + 1) * (wks.VPageBreaks.Count + 1), xlPortrait, xlLandscape)
            
  .Zoom = False
  .FitToPagesWide = 1
  .FitToPagesTall = False
End With

I would each sheet's print area to be the least number of A4 pages possible, optimised for landscape or portrait orientation.

I have one sheet where landscape orientation means 971 pages, but portrait cuts it to 455. I think I need to also adjust .FitToPagesWide and .FitToPagesTall, but have tried setting both to 1 (i.e. 1 page wide by 1 page tall), but this doesn't create the optimal print area.

Any suggestions?

TIA,
Jack

 

[steve the fish]

You had activesheet and then a sheet variable so that needed changing but apart from that its changing the orientation correctly for me.

With ActiveSheet.PageSetup
  .PaperSize = xlPaperA4
  .Orientation = xlPortrait
  x = (ActiveSheet.HPageBreaks.Count + 1) * (ActiveSheet.VPageBreaks.Count + 1)
  .Orientation = xlLandscape
  'Orientate the page for least number of page breaks
  .Orientation = IIf(x < (ActiveSheet.HPageBreaks.Count + 1) * (ActiveSheet.VPageBreaks.Count + 1), xlPortrait, xlLandscape)
End With

 

[JackDanIce]

Thanks Steve, sheet variable was argument being passed in, that first With should have been With wks.PageSetup, must have copied some testing code was using.

I think it is selecting the least page orientation but just now, running on colleague's PC, it "seemed" to be ignoring columns to the right and only showing 2 on a portrait page (expecting at least 5 columns). Keep at it..