I have written a VB app that generates an excel workbook, taking inputs from a third party application. The resulting workbook contains sheets of varying heights and widths.
When printed using default settings, the result is very messy, with data from the same worksheet spread across a number of pages.
I want to be able to automate the print formatting (either on creation of the workbook [VB - Excel Object Model], or by the user clicking on a "Format for Printing" toolbar button [VBA macro]), so that users don't have to mess around setting the PrintArea, Orientation, etc. Ideally, I want each worksheet to fit nicely to one printed page.
I have tried the following, with a varying degree of sucess: -
For each ws in ThisWorkbook
ws.PageSetup.PrintArea = ws.UsedArea.Address
ws.PageSetup.FitToPagesWide = 1
ws.PageSetup.FitToPagesTall = 1
ws.PageSetup.Zoom = False
Is there a better way of doing this?
The biggest problem I face is automating whether the orientation of a page should be Portrait or Landscape, depending on the number of rows / columns, or the height / width of the sheet.
Any help much appreciated.