CODEWITHSUNDEEP
excelvba
busy bee
busy bee

Reputation: 29

Print to pdf and fit rows on one page

The following code does not work correctly. I am attempting to print to pdf and fit all rows on one page. Any suggestions?

 For Each sheet1 In ActiveWorkbook.Worksheets
                With sheet1.PageSetup
                    .Orientation = xlLandscape
                    .Zoom = False
                    .FitToPagesTall = False
                    .FitToPagesWide = 1
                End With
            Next sheet1
            
            
         
         worbook1.ExportAsFixedFormat Type:=xlTypePDF, Quality:=xlQualityStandard, IncludeDocProperties:=False, IgnorePrintAreas:=False, OpenAfterPublish:=False

Upvotes: 0

Views: 1365

Answers (2)

busy bee
busy bee

Reputation: 29

I set both .FitToPagesTall = 1 and .FitToPagesWide = 1 and tried to execute it on a sample Excel file. It doesn't seem to work correctly. Below is the links to the Excel file and PDFs created when printed manually using Excel's 'Print-Fit all rows on one page' function and the one created by the VBA.

https://drive.google.com/file/d/1ohtP5VWz6_HnVVPYvcBnYlIciFKF3KlQ/view?usp=sharing

https://drive.google.com/file/d/1R0e8GBRFt9awFJgtD4VHhtV5ZVyUAA0s/view?usp=sharing

https://drive.google.com/file/d/1u851GmelwRX2_k1T5HgpFcQ1Hy2BLl4F/view?usp=sharing

Upvotes: 0

kzhong
kzhong

Reputation: 31

In order to put your worksheet in one printable page you will need to make both FitToPagesTall and FitToPageWide equal to 1.

See below for the revised code:

For Each sheet1 In ActiveWorkbook.Worksheets
                With sheet1.PageSetup
                    .Orientation = xlLandscape
                    .Zoom = False
                    .FitToPagesTall = 1
                    .FitToPagesWide = 1
                End With
            Next sheet1
            
worbook1.ExportAsFixedFormat Type:=xlTypePDF, Quality:=xlQualityStandard,_
IncludeDocProperties:=False, IgnorePrintAreas:=False, OpenAfterPublish:=False

Upvotes: 1

Related Questions