I am trying to build a macro in Excel which loops through all worksheets, and based on the largest sheet, sets the zoom level to the same level for all worksheets so they all fit on one page but have the same scale (which is needed in printing).
I am however having trouble with determining the zoom level which makes sure the biggest page fits to a 1 page width.
When setting a worksheets width to fit on one page by using .PageSetup.FitToPagesWide = 1
the .PageSetup.Zoom
property automatically gets set to FALSE.
Setting the FitToPage properties back to false, the zoom level is unchanged from what it was before fitting to one page.
When manually setting the sheet so it fits to one page wide, Excel does show which zoom level corresponds to this, but it seems there is no way to read this in VBA. Could someone help me with this issue?
This post is getting rather old, but as I've been sitting with a similar problem, this question gave me a possible answer.
Using a slightly redone code posted by Tom Urtis (https://www.mrexcel.com/forum/excel-questions/67080-page-setup-zoom-property.html) the following code extract the zoom iteratively, and then sets the zoom of all pages.