VBA Ignoring PageSetup Changes

Question

I have some VBA code set up to take a sheet name, range (prntRange), and orientation (prntOrient) and print the result, but it seems like VBA is ignoring the commands for the page setup. Here's a quick snippet:

For x = 1 To numPages
    Worksheets("Printing").Activate
    prntSheet = Cells(6 + (x - 1), 1)
    prntRange = Cells(6 + (x - 1), 2)
    prntOrient = Cells(6 + (x - 1), 3)

    Application.StatusBar = "Printing " & prntSheet & "..."

    Worksheets(prntSheet).Activate
    With ActiveSheet.PageSetup
        .PrintArea = False
        If prntOrient = "Portrait" Then
            .Orientation = xlPortrait
        Else
            .Orientation = xlLandscape
        End If
        .PrintArea = prntRange
        .Zoom = False
        .FitToPagesWide = 1
        .FitToPagesTall = 1
    End With
    ...

And here's an example of the table:

Sheet Name        Range        Orientation
 Example          A1:J56         Portrait
 Example          A1:J80         Landscape

prntRange pulls from Range, prntOrient pulls from Orientation

Basically what I'm looking for is a portrait version of the range A1:J56 of Example, and then a landscape version of the range A1:J80 of Example. The landscape version prints as two pages despite being told to be fit to one page. This happens whether or not that first entry is also there, and whether or not I manually reset the printing setting in Excel.

I've tried pretty much every permutation of the four commands above and order doesn't seem to make a difference. Setting .PrintArea = False was me thinking that refreshing the print area might help, but I don't believe it does. If I go through these steps manually then I have no problem getting it into the desired format, but the entire point of this macro is so that I don't have to do that.

Does anyone have any idea what I'm missing?

Thanks in advance!

EDIT: More accurate title.

EDIT 2: More detail per people's requests, sorry!

EDIT 3: Expanding code to show variable assignments

Have you checked if `prntOrient` is really `"Portrait"` including the first char in capital? If its value is `"portrait"` or anything different, it will never get off the landscape style. — Daniel Möller, Oct 08 '13 at 20:39
Also, is prntRange a string address like "A1:B2"? That's what's required. It would be helpful if you showed more code. — Doug Glancy, Oct 08 '13 at 20:41
Positive - that variable is pulling from data-validated cells with "Portrait" and "Landscape" as the only options. — Andy Phillips, Oct 08 '13 at 20:42
It would still be useful to see how you pull the table values into your code. At this point it would take guesswork, and if we don't do it the same way you do, it's hard to debug. — Doug Glancy, Oct 08 '13 at 23:38
This seems to be a problem to which few have an answer. Did you ever find a solution? I have a similar issue to which I have not yet found a working effective solution... http://stackoverflow.com/questions/24862064/excel-vba-not-exporting-pagesetup-to-pdf-correctly — rohrl77, Sep 17 '14 at 08:32

rohrl77 · Answer 1 · 2018-11-08T12:07:20.833

I have found a potential solution for problems i've had in similar siutations. Add Application.PrintCommunication to the process:

Application.PrintCommunication = False
    With ActiveSheet.PageSetup
        .Orientation = xlLandscape
        .Zoom = False
        '.PrintArea = Worksheets(ReportWsName).UsedRange
        .FitToPagesWide = 1
        '.FitToPagesTall = 1
    End With
Application.PrintCommunication = True

For whatever reason Excel would overwrite the settings I was putting if I ran the code without it.

VBA Ignoring PageSetup Changes

1 Answers1