sorry about that. Still learning as I go.

I'm also not sure if what is below will work for what you are looking for.
I've used this approach in other workbooks, but i've never needed to set
specific page breaks. Instead of trying to trap the zoom value after setting
to one page wide, this macro counts the number of rows in the worksheet
(assuming all rows are filled) and uses this to set the fittopagestall.

Hopefully this will work for you, or give you a jump start.

Sub SetPrintArea()
Dim NumRowsPerPrintPage As Integer
Dim PrntArea As Range
Dim NumRowsInWs As Integer

Range("A1").Select
Worksheets("Proposal-2").ResetAllPageBreaks

NumRowsPerPrintPage = "20"
NumRowsInWs = Application.WorksheetFunction.RoundUp _
(Application.WorksheetFunction.CountA(Range("A:A") ) / _
NumRowsPerPrintPage, 0)

With ActiveSheet.PageSetup
.PrintTitleRows = "$1:$12"
.LeftMargin = Application.InchesToPoints(0.78740157480315)
.RightMargin = Application.InchesToPoints(0.393700787401575)
.TopMargin = Application.InchesToPoints(0.31496062992126)
.BottomMargin = Application.InchesToPoints(0.393700787401575)
.HeaderMargin = Application.InchesToPoints(0.118110236220472)
.FooterMargin = Application.InchesToPoints(0.118110236220472)
.CenterHorizontally = False
.CenterVertically = False
.Orientation = xlLandscape
.Draft = False
.PaperSize = xlPaperA4
.FirstPageNumber = xlAutomatic
.Order = xlDownThenOver
.BlackAndWhite = False
.Zoom = False
.FitToPagesWide = 1
.FitToPagesTall = NumRowsInWs
End With


'
' Go back to the top of the worksheet
'
Range("A1").Select
'
' Find the 2nd occurrence of "Completion date"
'
Cells.FindNext(After:=ActiveCell).Activate
Cells.FindNext(After:=ActiveCell).Activate
'
' Go to column A
'
Range("A80").Select
'
' Insert Horizontal pagebreak
'
ActiveWindow.SelectedSheets.HPageBreaks.Add Befo=ActiveCell

End Sub

"Fred" wrote:

Again, yes, but no, not really.

The macro creates a new spreadsheet to contain the report, the default
setting for page layout at this point is Zoom to 100%. Once all the
cells have been populated with data, if I use the above code it finds
Zoom set at 100% (the default), so when it then goes through the page
setup code, it sets Zoom to be 100% (because that is what it found)
and, even though the Wide/Tall settings are there, because Zoom is not
False, they are ignored.

When I do the process manually Excel opens a new spreadsheet (default
Zoom factor is 100%), I then populate all cells and then go to Page
Setup, change from "Zoom" to "Fit To 1 page wide" and then OK. Excel,
somehow, updates the Zoom value at this point with the Zoom factor to
achieve the 1 page wide requirement, so that I can then go back into
Page Setup and change from "Fit To" back to "Zoom" and it will have the
correct Zoom factor for fitting to 1 page wide. The VB coding does not
seem able to pick this up and I think that unless there is somewhere
else to pick up the Zoom figure from (while Fit To is active) it will
always come up with the result of False because it has been told to
"Fit To" so Zoom will be false, if you see what I mean.

I suppose my question now is "When Fit To is active, is there somewhere
that I can pick up the Zoom factor from that it is using to achieve the
"Fit To" without getting an answer of False because Fit To is active"

Regards and thanks for all your help
Fred