I have a small macro that I wrote in Office2K that does some page setup
stuff - basically it sets the orientation, the margins to max, adds a
footer, and sets printing to 1 page wide (by x long).

Having just migrated from NT/Office2K to XP/Office2003 I now find that the
macro doesn't work - actually it does, but nothing happens (if that makes
sense...)

I tried recording the same actions to see where things had changed but the
only difference was that the recorded macro used .InchesToPoints instead of
the .CentimetersToPoints from my original code (see below)

my original code looks like this -

Sub PPSetup(appto As String, orient As String)
Dim i As Integer

For i = 1 To ActiveWorkbook.Sheets.Count
With Sheets(i).PageSetup
.LeftFooter = "&8&F - &8&A"
.CenterFooter = "&8&P of &N"
.RightFooter = "printed &T-&D"
.LeftMargin = Application.CentimetersToPoints(0.5)
.RightMargin = Application.CentimetersToPoints(0.5)
.TopMargin = Application.CentimetersToPoints(0.5)
.BottomMargin = Application.CentimetersToPoints(1)
.HeaderMargin = Application.CentimetersToPoints(0.5)
.FooterMargin = Application.CentimetersToPoints(0.5)
.TopMargin = Application.CentimetersToPoints(0.5)
.PrintGridlines = True
.Orientation = orient
.PaperSize = xlPaperA4
.Order = xlDownThenOver
.Zoom = False
.FitToPagesWide = 1
.FitToPagesTall = False
End With
If appto = "Sht" Then Exit For
Next i
End Sub

and the ('cleaned') recorded macro looks like this

Sub Marco1()
With ActiveSheet.PageSetup
.LeftFooter = "&F"
.CenterFooter = "&P of &N"
.RightFooter = "&A"
.LeftMargin = Application.InchesToPoints(0.196850393700787)
.RightMargin = Application.InchesToPoints(0.196850393700787)
.TopMargin = Application.InchesToPoints(0.196850393700787)
.BottomMargin = Application.InchesToPoints(0.393700787401575)
.HeaderMargin = Application.InchesToPoints(0)
.FooterMargin = Application.InchesToPoints(0.196850393700787)
.PrintGridlines = True
.PrintQuality = 600
.Orientation = xlLandscape
.PaperSize = xlPaperA4
.FirstPageNumber = xlAutomatic
.Order = xlDownThenOver
.Zoom = False
.FitToPagesWide = 1
.FitToPagesTall = False
.PrintErrors = xlPrintErrorsDisplayed
End With
End Sub

My original sub is called via a small form that prompts the user for the
preferred orientation (portrait or landscape), and whether or not they want
to apply the setup to just the active sheet, or to all the sheets in the
active workbook

any/all suggestions would be appreciated (cos I've got no idea...)

TIA

S

--
there are only 10 types of people in this world,
those who understand binary, and those who don't...

Hi Simon,

your original code works for me if I change type of "orient" to long
(excel expect one of constants xlLandscape = 2 or xlPortrait = 1).

Regards,
Ivan

sorry, forgot to mention that I am using XP/O2003

In our last episode Ivan Raiminius wrote:

sorry, forgot to mention that I am using XP/O2003


Ivan

many thanks, I noticed that yesterday afternoon, but figured that it
wouldn't make that much difference as many properties use/accept both
literal and numeric index values

actually, I have to admit it was my bad - the problem was with the loop, ie
it always started at the first sheet. It dawned on me last nite. I guess
that's the problem with writing stuff that you don't use yourself...

In case anyone's interested, the fix was to get the activesheet index, then
pass that to the existing loop so that if the user is only setting up a
single sheet the loop will start at the active sheet...

<snip

If appto = "Sht" Then ShtIndex = ActiveSheet.Index Else ShtIndex = 1

For i = ShtIndex To ActiveWorkbook.Sheets.Count

</snip

many thanks

S