Free Microsoft Excel 2013
Quick Reference
Free Microsoft 2013 Quick Reference Guide

Free Microsoft Excel 2013 Quick Reference

Activesheet.pagesetup

Hi,

Have an Excel spreadsheet that contains a macro that creates an new
worksheet and then fixes the pagesetup.

All the activesheet.pagesetup commands are painfully slow, but occasionally
they complete in the blink of an eye.

Can anyone help me to speed them up, or explain the inconsistent
performance?

Thanks,
Joe


Post your answer or comment

comments powered by Disqus
I am using Excel 2003 SP2 to create a series of macros, one of which is to allow the user to print a portion of a worksheet created through another macro.

The macro detects the print area then uses ActiveSheet.PageSetup commance to set the various page setup options - margins, layout, etc.

I am on a network and have noticed through debug.print statements that each ActiveSheet.PageSetup commands takes about 5 seconds and since there are roughly 30 of them, it takes quite awhile before the printer dialogue box appears.

Here is the macro:

Sub PrintPricingDocument()
Sheets("Pricing Document").Select
Range("A1").Select
Selection.End(xlDown).Select
strLoc = Selection.Address
numLoc = Mid$(strLoc, 4, 2) 'gives the row number for the bottom of the print range
StrLoc2 = "$A$1:$G$" + CStr(numLoc)
Range("A1").Select
ActiveSheet.PageSetup.PrintArea = StrLoc2
Range("A1").Select
ActiveSheet.PageSetup.PrintTitleRows = ""
ActiveSheet.PageSetup.PrintTitleColumns = ""
ActiveSheet.PageSetup.LeftHeader = ""
ActiveSheet.PageSetup.CenterHeader = ""
ActiveSheet.PageSetup.LeftFooter = ""
ActiveSheet.PageSetup.CenterFooter = ""
ActiveSheet.PageSetup.RightFooter = ""
ActiveSheet.PageSetup.LeftMargin = Application.InchesToPoints(0.75)
ActiveSheet.PageSetup.RightMargin = Application.InchesToPoints(0.75)
ActiveSheet.PageSetup.TopMargin = Application.InchesToPoints(1)
ActiveSheet.PageSetup.BottomMargin = Application.InchesToPoints(1)
ActiveSheet.PageSetup.HeaderMargin = Application.InchesToPoints(0.5)
ActiveSheet.PageSetup.FooterMargin = Application.InchesToPoints(0.5)
ActiveSheet.PageSetup.PrintHeadings = False
ActiveSheet.PageSetup.PrintGridlines = False
ActiveSheet.PageSetup.PrintComments = xlPrintNoComments
ActiveSheet.PageSetup.PrintQuality = 600
ActiveSheet.PageSetup.CenterHorizontally = True
ActiveSheet.PageSetup.CenterVertically = False
ActiveSheet.PageSetup.Orientation = xlLandscape
ActiveSheet.PageSetup.Draft = False
ActiveSheet.PageSetup.PaperSize = xlPaperLetter
ActiveSheet.PageSetup.FirstPageNumber = xlAutomatic
ActiveSheet.PageSetup.Order = xlDownThenOver
ActiveSheet.PageSetup.BlackAndWhite = False
ActiveSheet.PageSetup.Zoom = False
ActiveSheet.PageSetup.FitToPagesWide = 1
ActiveSheet.PageSetup.FitToPagesTall = 1
ActiveSheet.PageSetup.PrintErrors = xlPrintErrorsDisplayed
Application.Dialogs(xlDialogPrint).Show
End Sub

I have tried the above statements in a With statement but no difference. I suspect this has something to do with the printer being on the company network?! Finally, I noticed that, in one instance after running my print macro, my other macros (invoked separately - not called by this macro) also slowed down - but this only happened once in several tests. Any ideas? Thanks in advance.

I'm trying to set the pagesetup to Fit 1 page wide in code. The number of
pages will vary from 1 to 50, so i have no way to predict that. So, i do
the following:
With ActiveSheet.PageSetup
.Zoom = True
.FitToPagesWide = 1
.FitToPagesTall = 99
End With

I receive an error at the .Zoom = True

When i look at the page setup, i see the 1 and 99, but it does not show me
the "Fit to:" option selected.

Thank you,

Mark

Hi,

I have a macro that prints the content od a range of cells and i have it set
to print D,142 to AA,142 using ActiveSheet.PageSetup.PrintArea =
"$D$142:$AA$142"

Every now and then the row changes (people adding/deleting rows) so I have
created a row variable by searching for a string that only appears in the
row I want to print as follows;

CtoPrintRow = "CTO Requests"
r = 1
Do Until CtoPrintRow = (Cells(r, 1))
r = r + 1
Loop

I can't get my head around how I use now use the variable to print that row,
I have fiddled with it as follows but clearly I am missing something, but
what ?

ActiveSheet.PageSetup.PrintArea = "$D$r:$AA$r"

The macro below was created by recording, I replaced the string of text with variables (sMyArray) in .LeftHeader..
Why do I get a font size of "409" in .LeftHeader, when .CenterHeader works fine with a string?
    With ActiveSheet.PageSetup
        .LeftHeader = "&""Arial,Bold""&16" & sMyArray(0) & " " &
sMyArray(1) & "&""Arial,Regular""&8" & "03.04.11"
        .CenterHeader = "&""Arial,Bold""&16CMM Operation completion Record"
        .RightHeader = ""
    End With
Adding to this, the .LeftHeader text is only a partial string.
"B Y .04.11"

Any hints tips or examples are appreciated.

I'm looking for either a solution to my immediate problem OR any other clever workaround you can propose.

Basically, I do work that requires several printable areas ("exhibits") per sheet. I like each exhibit I print to take up most of the page, so I use the "fit to 1 page long by 1 page wide" setting in the page setup. However, when you have one print area that's really huge and another that's really small, both on the same sheet, using this setting will size all of the pages to the largest exhibit. The result is that excel shrinks the text of ALL the exhibits. This is good for the huge print area - all the text fits on the page when you print - but for the smaller print areas, you get this little blob of text because it's shrinking the text by the same factor as for the large print area.

To work around this, I devised a macro that copies the page's pagesetup.printarea to a separate variable, then splits that printarea everywhere that there are commas:


	VB:
	
Print_Area_Text = ActiveSheet.PageSetup.PrintArea 
Split_Print_Area = Split(Print_Area_Text, ",") 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
So if the PrintArea is "A1:C5,D1:F5" then you get Split_Print_Area(0) = "A1:C5" and Split_Print_Area(1) = "D1:F5". Follow me so far?

So I loop through each item in my Split_Print_Area array, and for each item I set the printarea equal to the split_print_area(i), then print. Because I'm printing each page individually, excel resizes the page for that page ONLY, so every page fits screen properly. The code looks something like this:


	VB:
	
(Split_Print_Area) 
    ActiveSheet.PageSetup.PrintArea = Split_Print_Area(i) 
    ActiveSheet.PrintOut Copies:=1 
Next i 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
Works like a charm. The problem is that this destroys the original print area. So at the end, I want to go back and set the PrintArea equal to my ORIGINAL print area text:

	VB:
	
ActiveSheet.PageSetup.PrintArea = Print_Area_Text 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
Here's where we run into problems. USUALLY this is fine - it sets the print area back to the original print area text. But if Print_Area_Text is too long, Excel throws an error. The limit seems to be about 250 characters. On rare occasions, I do actually have print areas that are this long. For example, I have a workbook right now with a printarea equal to:

	VB:
	
"A1:I32,K1:S32,U1:AC32,AE1:AM32,A36:F54,A58:F81,K58:P81,U58:Z81,AE58:AJ81,A85:F109,A113:F137,A141:F165,A169:F193,A197:H221,A225:F249,A253:H277,A281:H305,A309:F333,A337:H361,A365:F389,A393:F417,A421:F445,A449:F473,O85:AC109,O113:AC137,O141:AC165,O169:AC193,O197:AF221,O225:AC249,O253:AF277,O281:AF305,O309:AC333,O337:AF361,O365:AC389,O393:AC417,O421:AC445,O449:AC473"


If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
Please don't advise me to "cut down on the number of sheets per page" - I KNOW this is a workaround, but it isn't a very USEFUL one for me.

So it's curious to me: If I hit record macro, mess with the printarea, then stop recording, it gives me a macro that includes some line like:

	VB:
	
Activesheet.Pagesetup.PrintArea =
"A1:I32,K1:S32,U1:AC32,AE1:AM32,A36:F54,A58:F81,K58:P81,U58:Z81,AE58:AJ81,A85:F109,A113:F137,A141:F165,A169:F193,A197:H221,A225:F249,A253:H277,A281:H305,A309:F333,A337:H361,A365:F389,A393:F417,A421:F445,A449:F473,O85:AC109,O113:AC137,O141:AC165,O169:AC193,O197:AF221,O225:AC249,O253:AF277,O281:AF305,O309:AC333,O337:AF361,O365:AC389,O393:AC417,O421:AC445,O449:AC473"


If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
When I run the macro I just recorded, it docks the printarea string and preserves only about half the sheets. Yet I can set the printarea to greater than 250 characters in two ways: I can add each print range manually (by selecting the range, right clicking, and hitting "add to print range" on the excel sheet) or by typing in the behemoth print range in the "page setup" dialog. So how do I reproduce this in vba? Is there some workaround for setting a print range that is greater than 250 characters?

Thanks,
MBDaniel

I have a procedure which sets the zoom property in page setup:


	VB:
	
ActiveSheet.PageSetup.zoom = 78 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
The probem is that this does not adjust the automatic page breaks. If I manually go into Page Setup under the File menu, the scale has been adjusted to 78%. If I press OK then the page breaks will automatically change. How do I get the page breaks to update in VBA? I need the page breaks to update since later in the procedure I find them to modify the worksheet (add rows beneath that say "Continued"). Any help will be greatly appreciated. Thanks.

I am using some old code that I wrote/recorded for an Excel Spread Sheet version 2003. Now I'd like to use the same bit of code in Excel but an earlier version (2000).

This bit of code basically formats the active sheet. It works for the Excel version 2003 that I originally used it in but fails when copying it to version 2000.


	VB:
	
With ActiveSheet.PageSetUp 
    .PrintComments = xlPrintNoComments 
    .CenterHorizontally = True 
    .Orientation = xlLandscape 
    .PaperSize = xlPaperLetter 
    .FirstPageNumber = xlAutomatic 
    .Order = xlDownThenOver 
    .FitToPagesWide = 1 
    .FitToPagesTall = 1 
End With 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
I get a run time error '1004': Unable to Set to set the CenterHorizontally property if the PageSetUp class.

the code works the following way in ver. 2000:


	VB:
	
With ActiveSheet.PageSetUp 
    .PrintComments = xlPrintNoComments 
    .FitToPagesWide = 1 
    .FitToPagesTall = 1 
End With 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
why can't I include the the following class properties:


	VB:
	
 
.Orientation = xlLandscape 
.PaperSize = xlPaperLetter 
.FirstPageNumber = xlAutomatic 
.Order = xlDownThenOver 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
Thank you in advance to whom ever may help.

I am trying to determine what the PageSetup zoom value is (not the window zoom) when FitToPagesWide is set. For example, the following code:


	VB:
	
 ActiveSheet.PageSetup 
    .zoom = False 
    .FitToPagesWide = 1 
    .FitToPagesTall = False 
End With 
MsgBox (ActiveSheet.PageSetup.zoom) 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
will return "False". However, if I manually go into PageSetup under the File menu, the Adjust To box displays what the sheet is zoomed to with FitToPagesWide = 1 set. How do I determine this value in VBA? I need to determine this value so that the zoom is not changed when columns are hidden (otherwise the header size changes and encroaches into the text of the table when printed). Any help would be appreciated. Thanks.

I am getting a runtime error when using the PageSetup.PaperSize command in VBA. I am using Excel 2004 for Mac OS X. Here is the code:

With ActiveSheet.PageSetup
.LeftHeader = ""
.CenterHeader = ""
.RightHeader = ""
.LeftFooter = ""
.CenterFooter = "Page &P of &N"
.RightFooter = ""
.LeftMargin = Application.InchesToPoints(0)
.RightMargin = Application.InchesToPoints(0)
.TopMargin = Application.InchesToPoints(0.5)
.BottomMargin = Application.InchesToPoints(0.75)
.HeaderMargin = Application.InchesToPoints(0.5)
.FooterMargin = Application.InchesToPoints(0.5)
.PrintHeadings = False
.PrintGridlines = False
.PrintComments = xlPrintNoComments
.PrintQuality = -4
.CenterHorizontally = False
.CenterVertically = False
.Orientation = xlLandscape
.Draft = False
.FirstPageNumber = xlAutomatic
.Order = xlDownThenOver
.BlackAndWhite = False
.Zoom = 100
.PaperSize = xlPaperLegal
End With

The .PaperSize command is not working.

Thanks for any help you can offer me.

I am migrating automated reports from an old NT+Excel2000 to a XP+ExcelXP machine. I copied XLODBC.XLA from the old machine, and this solved most of my problems. The only one that remains is as follows.

VBA "Runtime Error '1004': Unable to set the CenterHeader parameter of the PageSetup class"

If I remove that, I get the same error for the next parameter below. It doesn't like any of the lines inside the With.

Code:
With ActiveSheet.PageSetup  'Set up titles and header title
     .CenterHeader = "&8Header " '& myMonth
     .Zoom = 100
     .Orientation = xlLandscape
     .Order = xlOverThenDown
End With
Have things changed between Excel versions? How can I fix this?

Any help greatly appreciated,

Maks

Cross posted since I have gotten no responce in
microsoft.public.excel.programming for 3 days

Ok I've seen the 1,000s of people trying to program page setup
options
and having a bad time. So let me add more problems.

I was using Excel 2000. I now have 2007. I had a workaround for
this
bug in 2000/2003 but I can't seem to get around it in 2007 (and of
course it isn't fixed)

Here is what I'm trying to do:
Set the page to Landscape, 1 page wide
Then add some intelligent page breaks by looking for the automatic
page break and putting a manual break above it where the data
recently
changed.

The problem is that the automatic page break is not recalculated
after
setting the zoom factor programatically.

if I do:
With ActiveSheet.PageSetup
.Orientation = xlLandscape
.Zoom = 65
End With

the first page break is on row 33

In 2000/2003 I could :
SendKeys "%(FUA)" + Format(ActiveSheet.PageSetup.Zoom) + "~",
True

This is a crappy way to get around it but after the sendkeys the
first
page break would not be row 53

I cannot get a key stroke method to the page setup dialog box in
Excel2007 to work from sendkeys (I can do it manually but that
doesn't
help)

Anybody know how to get Excel to recalculate the page breaks?
Obviously something is being called from teh page setup dialog box
that I can't find directly.

Thanks for any help.
Pete

Below is a page setup macro that runs real fast on my Excel2000 WinXP Pro
Desktop but real slow on Excel2000 WinXP Home Laptop.

I put msgboxes in to see what was slowing this macro up and the .TopMargin,
..BottomMargin, .Orientation and .PrintHeadings seemed to be taking a lot of time
to finish.

Any suggestions on how to speed this code up?

Thanks.

Dennis

Sub Page_Setup()

With ActiveSheet.PageSetup
.PrintTitleRows = "$1:$1"
.RightHeader = "&F &D &T &P/&N"
.TopMargin = Application.InchesToPoints(0.75)
.BottomMargin = Application.InchesToPoints(0.5)
.Orientation = xlLandscape
.Zoom = 75
.PrintHeadings = True
.PrintGridlines = True
End With
End Sub

why is it that my code for a macro that gets called in the personal workbook doesnt work properly. It is With ActiveSheet.PageSetup
.Zoom = 97
End With
it works good for future workbooks, but when i go back and look at an old workbook that didn't have tis code in it the message pops up as object variable or with block variable not set?

I need a macro that will select all sheets within the workbook and make changes to the PageSetup and then ungroup the sheets within naming a particular sheet since I won't know the sheet names. I wrote the below code that partially works. It doesn't change Pagesetup for all sheets nor does it ungroup all sheets without using a sheet name. Can someone tell me what I am doing wrong? Thanks.

Sheets.Select
    With ActiveSheet.PageSetup
        .LeftHeader = ""
        .CenterHeader = ""
        .RightHeader = ""
        .LeftFooter = "&8&Z&F - &A -&D"
        .CenterFooter = ""
        .RightFooter = ""
        .LeftMargin = Application.InchesToPoints(0.25)
        .RightMargin = Application.InchesToPoints(0.25)
        .TopMargin = Application.InchesToPoints(1.25)
        .BottomMargin = Application.InchesToPoints(0.3)
        .HeaderMargin = Application.InchesToPoints(0)
        .FooterMargin = Application.InchesToPoints(0.15)
        .PrintHeadings = False
        .PrintGridlines = False
        .PrintComments = xlPrintNoComments
        .PrintQuality = 1200
        .CenterHorizontally = True
        .CenterVertically = False
        .Orientation = xlLandscape
        .Draft = False
        .PaperSize = xlPaperLetter
        .FirstPageNumber = xlAutomatic
        .Order = xlDownThenOver
        .BlackAndWhite = False
        .Zoom = False
        .FitToPagesWide = 1
        .FitToPagesTall = 1
        .PrintErrors = xlPrintErrorsDisplayed
        .OddAndEvenPagesHeaderFooter = False
        .DifferentFirstPageHeaderFooter = False
        .ScaleWithDocHeaderFooter = True
        .AlignMarginsHeaderFooter = True
        .EvenPage.LeftHeader.Text = ""
        .EvenPage.CenterHeader.Text = ""
        .EvenPage.RightHeader.Text = ""
        .EvenPage.LeftFooter.Text = ""
        .EvenPage.CenterFooter.Text = ""
        .EvenPage.RightFooter.Text = ""
        .FirstPage.LeftHeader.Text = ""
        .FirstPage.CenterHeader.Text = ""
        .FirstPage.RightHeader.Text = ""
        .FirstPage.LeftFooter.Text = ""
        .FirstPage.CenterFooter.Text = ""
        .FirstPage.RightFooter.Text = ""
    End With
    Sheets("Sheet1").Select
End Sub


Hi,

I am trying to set the Scaling in Page setup to an optimal zoom %.

I set the Fit to pages wide to say 1 then I want to check what the resulting
zoom to see if I should set it to 2 or more pages wide. But I am having
problems with the PageSetup.Zoom function:

With ActiveSheet.PageSetup
.Zoom = False
.FitToPagesTall = False
.FitToPagesWide = 1
.PrintTitleColumns = False
End With
For x = 2 To 100
With ActiveSheet.PageSetup
If .Zoom < tbxMin_Zoom_Percent.Value Then
.FitToPagesWide = x - 1
x = 100
Else
.FitToPagesWide = x
End If
End With
Next x

Problem:
After setting the pages wide = 1, I want to check what Zoom % that Excel
calculated, then if it is smaller than the Text Box "tbxMin_Zoom_Percent",
then try setting the pages wide to 2, then check the zoom again until the
zoom % is higher than user's selected Minimum.

Problem is the code:
If .Zoom < tbxMin_Zoom_Percent.Value Then
doesn't work

thanks

Simon

Below is a page setup macro that runs real fast on my Excel2000 WinXP Pro
Desktop but real slow on Excel2000 WinXP Home Laptop.

I put msgboxes in to see what was slowing this macro up and the .TopMargin,
..BottomMargin, .Orientation and .PrintHeadings seemed to be taking a lot of time
to finish.

Any suggestions on how to speed this code up?

Thanks.

Dennis

Sub Page_Setup()

With ActiveSheet.PageSetup
.PrintTitleRows = "$1:$1"
.RightHeader = "&F &D &T &P/&N"
.TopMargin = Application.InchesToPoints(0.75)
.BottomMargin = Application.InchesToPoints(0.5)
.Orientation = xlLandscape
.Zoom = 75
.PrintHeadings = True
.PrintGridlines = True
End With
End Sub

I want to add a header as below. However, I want to replace the word "Test"
with a variable. How do I do that and still retain the formatting?

With ActiveSheet.PageSetup
.CenterHeader = "&""Arial,Bold""&14Test"
End With

Thanks

Right now, I'm in MS Access VBA and doing:

-------------------------------------------------
1070 With mySS.ActiveSheet.PageSetup
1071 .LeftHeader = ""
1072 .leftMargin = 0
1073 .RightMargin = 0
1074 .topMargin = 0
1075 .bottomMargin = 0
1079 .HeaderMargin = 0
1080 .FooterMargin = 0
1081 .Orientation = xlLandscape
1082 .FitToPagesWide = 1
1083 .FitToPagesTall = 1
1089 End With
-------------------------------------------------

This works, but takes a long time - it's like Excel is
re-drawing/reformatting the page for each property instead of
redrawing once at "End With".

I can live with that for one sheet, but this app is creating a
half-dozen sheets in one workbook and the wait time will be too long
if multiplied by six.

Seems like there sb some kind of default in MS Excel that I could set
once when opening up the workbook and then be inherited by each
worksheet I create..... or some way to create a worksheet using an
existing sheet as the template.

Anybody know?

How about the root performance issue? Am I doing something dumb in
the "With" clause?

Hi everyone,

I've recorded my changes into a macro so I hoped I'd be off the hook but it doesn't seem to have any effect. After executing the marcro, the workbook is still in portrait mode instead of landscape, and the margins are unaltered.

The only thing I can think of at the moment, is that the macro code resides in personal.xls. Maybe that prevents the macro from altering things in other workbooks? It has to be there so that the marco is accessible to every workbook.

Below is the Excel generated code

cheers,

Jeroen

With
ActiveSheet.PageSetup
        .PrintTitleRows = ""
        .PrintTitleColumns = ""
    End With
    ActiveSheet.PageSetup.PrintArea = ""
    With ActiveSheet.PageSetup
        .LeftHeader = ""
        .CenterHeader = ""
        .RightHeader = ""
        .LeftFooter = ""
        .CenterFooter = ""
        .RightFooter = ""
        .LeftMargin = Application.InchesToPoints(0.354330708661417)
        .RightMargin = Application.InchesToPoints(0.354330708661417)
        .TopMargin = Application.InchesToPoints(0.393700787401575)
        .BottomMargin = Application.InchesToPoints(0.393700787401575)
        .HeaderMargin = Application.InchesToPoints(0.31496062992126)
        .FooterMargin = Application.InchesToPoints(0.31496062992126)
        .PrintHeadings = False
        .PrintGridlines = False
        .PrintComments = xlPrintNoComments
        .CenterHorizontally = False
        .CenterVertically = False
        .Orientation = xlLandscape
        .Draft = False
        .PaperSize = xlPaperA4
        .FirstPageNumber = xlAutomatic
        .Order = xlDownThenOver
        .BlackAndWhite = False
        .Zoom = False
        .FitToPagesWide = 1
        .FitToPagesTall = 1
        .PrintErrors = xlPrintErrorsDisplayed
        .OddAndEvenPagesHeaderFooter = False
        .DifferentFirstPageHeaderFooter = False
        .ScaleWithDocHeaderFooter = True
        .AlignMarginsHeaderFooter = True
        .EvenPage.LeftHeader.Text = ""
        .EvenPage.CenterHeader.Text = ""
        .EvenPage.RightHeader.Text = ""
        .EvenPage.LeftFooter.Text = ""
        .EvenPage.CenterFooter.Text = ""
        .EvenPage.RightFooter.Text = ""
        .FirstPage.LeftHeader.Text = ""
        .FirstPage.CenterHeader.Text = ""
        .FirstPage.RightHeader.Text = ""
        .FirstPage.LeftFooter.Text = ""
        .FirstPage.CenterFooter.Text = ""
        .FirstPage.RightFooter.Text = ""
    End With


I first attempted to record a macro for changing the page setup to print 2 tall by 1 wide. (code below)

I received an error message "Object variable or with block variable not set".

This macro is going inside of an excel add-in. Which leaves me to believe this may be a referencing error.

    With ActiveSheet.PageSetup
        .PrintTitleRows = ""
        .PrintTitleColumns = ""
    End With
    ActiveSheet.PageSetup.printarea = ""
    With ActiveSheet.PageSetup
        .Orientation = xlLandscape
        .Zoom = False
        .FitToPagesWide = 1
        .FitToPagesTall = 2

    End With
There were more options inside the WITH statement, but I shrunk it down for reading purposes. I tried searching for solutions online and tried a few snippets from others, but all came back with this error message.

Any ideas?

Thanks in advance!

Ok using the code below I create a duplicate of my Traffic sheet for A4 paper
printout. However, the FitToPagesWide and FitToPagesTall do not seem to
affect the new page. It stays as the original which is set at "Adjust to:
44%". Is there a way to clear the "Adjust to" field to allow the
FitToPagesWide and Tall will work?

'Create A4 sheet
Sheets(ActiveSheet.name).Copy After:=Sheets(ActiveSheet.name)
ActiveSheet.name = "Traffic [A4]" 'Names sheet
With ActiveSheet.PageSetup
.FitToPagesWide = 1
.FitToPagesTall = 1
.Orientation = xlLandscape
.PaperSize = xlPaperA4
End With

Hi

I am getting error:
Run-time error '1004': Unable to set the Orientation property of the PageSetup class

I am using the following code:
Dim StrOrientation As String
Application.Goto Reference:="Orientation"
StrOrientation = Activecell.Value 'where reference is either xlPortrait of xlLandscape


    With ActiveSheet.PageSetup
        
         .Orientation = StrOrientation 

    End With
In the .Orientation = StrOrientation section I have replaced the variable with "xlPortrait" and it works so I know it isn't a printer issue. I have entered the variable reference in incorrectly but I cannot work out where or how.

Thanks in advance

Excel version: 11 (2003 SP2)
OS: WXP SP2

In a procedure to print user-specified pages, I have a section of the code that forces manual page breaks but the pages breaks are not taking effect. The procedure itself is long - some 300 lines. In summary, before the code that supposed to set the pages, the procedure:
Asks the financial quarter the user wants to printThen asks how many pages the user wants to printSets the PrintArea accordingly
Then follows this code:

    ' Remove all manual page breaks
    ActiveSheet.ResetAllPageBreaks
    ' We set-up important PageSetup parameters set at beginning,
    ' so just set PrintArea
    With ActiveSheet.PageSetup
        .PrintArea = PA_PrintRange
    End With
    With ActiveSheet.HPageBreaks
        ' Force specific horizontal page breaks
        .Add Before:=ActiveSheet.Range("B53") ' Page 1
        .Add Before:=ActiveSheet.Range("B96") ' Page 2
        .Add Before:=ActiveSheet.Range("B139") ' Page 3
    End With
When I put the ActiveSheet into PageView mode and step through the ".Add Before" statements, nothing at all happens.

Since I'm dealing with protected sheets, prior to the above code the procedure unprotects the worksheet.

What have I done incorrectly?

Thanks,

- Al

I'm using the PageSetup class (late binding) in my application to set the
following.

ActiveSheet.PageSetup.Orientation = 2 ' Landscape
ActiveSheet.PageSetup.LeftMargin = 36
ActiveSheet.PageSetup.RightMargin = 36
ActiveSheet.PageSetup.TopMargin = 54
ActiveSheet.PageSetup.BottomMargin = 54
ActiveSheet.PageSetup.RightFooter = "Page &P of &N"

However, it depends on which environment I run the application as to whether
or not it works.

OS Excel Result
Windows XP Pro v2002 SP2 2003 (11.6355.6360) SP1 Always Works
Windows NT SP4 2003 (11.6355.6360) SP1 Does Not Work
Windows XP Pro v2002 SP2 2002 (10.6501.6735) SP3 Does Not Work

When it doesn't work, I receive the error message
"Unable to set the Orientation property of the PageSetup class"

If I comment out the Orientation property line, I receive the error message
"Unable to set the LeftMargin property of the PageSetup class"

Only when I comment out all the lines, will the application run without
error under the environments mentioned above.

Any ideas?


No luck finding an answer? You could always try Google.