[VBA] Change Paper Size , Print, Change Back

[CWatts]

I currently have a document formatted to print on legal paper. It works great when printing to one of our large workgroup printers which has both letter and legal paper trays and will appropriately use Legal paper to print.

Not everyone wants to print the documents to the big printer and they'd prefer to use their personal printers which are letter only. To acommodate this request I wrote the below macro to change the paper size to legal and fit it to one page.

Sub LetterPrint()
    With Sheets("MetricStack").PageSetup
        .PaperSize = xlPaperLetter
        .FitToPagesWide = 1
        .FitToPagesTall = 1
    End With
 
    Sheets("MetricStack").PrintOut
 
    With Sheets("MetricStack").PageSetup
        .PaperSize = xlPaperLegal
        .Zoom = 100
    End With
 
End Sub

The proiblem is when myself or someone else tries printing to one of our printers with letter only, the page setup code in the macro seems ineffective.

Iknow the code works becuase when printing to one of the multi-trayed workgroup printers the paper size and zoom adjusts accordingly and sets itself back.

Each time have tested this, I've made sure the default printer was set and I made sure that if Excel were open, I'd close it and reopen it to ensure it would recognize the default printer.

The different printer without legal papers seems to be the only X factor, but I'm at a loss for how to code around that. Anyone have any tips?

 

[Mrnutle]

I cribbed your code for some stuff I was working on. One thing I noticed, and haven't resolved yet. You set your "FitToPage" numbers but you have to make sure the print settings are set to use that instead of the "Zoom" option. I ran the code through and even though it changed my

.FitToPagesWide = 1
.FitToPagesTall = 1

the page setup was still set to use the zoom option.

 

[CWatts]

I got stuck there as well, I tried with the macro recorder and lined the commands up side by side and the only code that I'd see change was going to the pages, and then when I recorded the switch back, the only change was the zoom..

I ultimately ended up "solving" my needs by creating a hidden sheet formatted at Letter / Fit to Page with a whole bunch of =MetricStack!A1 type references to the cells that needed it.

It fit the bill for now, but I'm still holding out hope that someone will know the line of code that is eluding us.