Macro for modifying Page Setup - only part of it works

jrnyman · Apr 11th, 2002, 08:34 AM

I've written this loop to modify the page setup for each page I need to print. When i step through it, it does indeed go to each page, but when it's done and I manually check the settings, the FitToPages...settings never stick. The footer is fine, but I can't tell it to print each page on ONE page. Anyone think they can help? I got the code from a recorded macro.

For ContentCtr = 3 To (SheetCounter + 1) 'SheetCounter is defined earlier as thhe number of sheets I'm interested in
Sheets(ContentCtr).Select
ActiveSheet.PageSetup.PrintArea = ""
ActiveSheet.PageSetup.FitToPagesWide = 1
ActiveSheet.PageSetup.FitToPagesTall = 1
ShtNum = ContentCtr
Sheets(ContentCtr).PageSetup.RightFooter = "Page " & ShtNum
Next ContentCtr

MrExcel · Apr 11th, 2002, 08:57 AM

You need to set the .Zoom to False.

ActiveSheet.PageSetup.Zoom = False

The recorded macro should have done that for you. I realize many of those recorded lines are extraneous, but that one is necessary.

Bill

_________________
MrExcel.com Consulting

[ This Message was edited by: MrExcel on 2002-04-11 07:57 ]

jrnyman · Apr 11th, 2002, 09:12 AM

Perfect.

I don't know where you pick up these things, but that certainly did the trick. Thanks.

JrnyMan
Youngstown, Ohio
(presently in London)

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

Apr 11th, 2002, 08:34 AM	#1
jrnyman Board Regular Join Date: Mar 2002 Location: Boston, MA Posts: 105	I've written this loop to modify the page setup for each page I need to print. When i step through it, it does indeed go to each page, but when it's done and I manually check the settings, the FitToPages...settings never stick. The footer is fine, but I can't tell it to print each page on ONE page. Anyone think they can help? I got the code from a recorded macro. For ContentCtr = 3 To (SheetCounter + 1) 'SheetCounter is defined earlier as thhe number of sheets I'm interested in Sheets(ContentCtr).Select ActiveSheet.PageSetup.PrintArea = "" ActiveSheet.PageSetup.FitToPagesWide = 1 ActiveSheet.PageSetup.FitToPagesTall = 1 ShtNum = ContentCtr Sheets(ContentCtr).PageSetup.RightFooter = "Page " & ShtNum Next ContentCtr

Apr 11th, 2002, 08:57 AM	#2
MrExcel . Join Date: Feb 2002 Location: Akron, Ohio USA Posts: 789	You need to set the .Zoom to False. ActiveSheet.PageSetup.Zoom = False The recorded macro should have done that for you. I realize many of those recorded lines are extraneous, but that one is necessary. Bill _________________ MrExcel.com Consulting [ This Message was edited by: MrExcel on 2002-04-11 07:57 ]

Apr 11th, 2002, 09:12 AM	#3
jrnyman Board Regular Join Date: Mar 2002 Location: Boston, MA Posts: 105	Perfect. I don't know where you pick up these things, but that certainly did the trick. Thanks. JrnyMan Youngstown, Ohio (presently in London)