Macro to accomplish Zoom and Fit to Simultaneously

[CodeCrazy]

Hello -
I currently have an excel workbook that contains multiple worksheets. Each
worksheet contains four sections (one right after the next) that all start
out with the same number of rows. All rows that total to zero are hidden,
making each worksheet and section different printing ranges. I need each
worksheet to print on four pages (one section on each) with the page breaks
at row 144, 283, and 422. Is it possible, and how would I go about, setting
the .fittopageswide = 1 and .fittopagestall = 4 and adjusting the page breaks
to the specified rows? It appears that I can only do one or the other. This
is messing up the zoom on some worksheets. I have the following code:

activewindow.view = xlpagebreakpreview (this is only here so that I can
watch my as I step through the code)

with activesheet.pagesetup
.fittopageswide = 1
.fittopagestall = 4
endwith
activesheet.resetallpagebreaks
set activesheet.hpagebreaks(1).location = range("E144")
set activesheet.hpagebreaks(2).location = range("E283")
set activesheet.hpagebreaks(3).location = range("E422")
activewindow.view = xlnormalview

I am sure that the reset all is taking away the fit to 1 by 4. Any
suggestions on how to accomplish this? Thanks in advance for your time.
CodeCrazy

 

[Vergel Adriano]

The Resetallpagebreaks doesn't take away the 1x4 setting.. but you need to
set the zoom property to False, for the setting to apply.. Try:


With ActiveSheet.PageSetup
.FitToPagesWide = 1
.FitToPagesTall = 4
.Zoom = False
End With
ActiveSheet.ResetAllPageBreaks
ActiveSheet.HPageBreaks.Add Range("E144")
ActiveSheet.HPageBreaks.Add Range("E283")
ActiveSheet.HPageBreaks.Add Range("E422")
ActiveWindow.View = xlNormalView

 

[CodeCrazy]

That did it. Thanks so much!

The Resetallpagebreaks doesn't take away the 1x4 setting.. but you need to
set the zoom property to False, for the setting to apply.. Try:


With ActiveSheet.PageSetup
.FitToPagesWide = 1
.FitToPagesTall = 4
.Zoom = False
End With
ActiveSheet.ResetAllPageBreaks
ActiveSheet.HPageBreaks.Add Range("E144")
ActiveSheet.HPageBreaks.Add Range("E283")
ActiveSheet.HPageBreaks.Add Range("E422")
ActiveWindow.View = xlNormalView