Vba: Print an Excel sheet to multiple pdf pages

Question

I'll try to keep this short. I'm working on a Excel project and I have to print at the end a single sheet, the problem is the printed PDF is too small, so I've looked up for it in different forums and I found out that I had to turn .FitToPagesTall = 0 In order that the excel worksheet don't get fit to one pdf page. The problem I'm struggling with now is that even if the pdf pages are bigger than before, It still small and It's making it hard to read. My idea is to print each 30 rows (for example) in a pdf page (Page1 --> Range("A1:E30"), Page2 --> Range("A31:E60").. etc, you got the idea)

Any ideas how I can do that please ?

Thanks in advance !

Update #1: Here's a screen shot of only a slice of my data range

Even if I set the .PrintArea to A:D, it still give the same result. To rephrase my request: I'm looking for a way to print different ranges in multiple pages.

thanks everyone for giving me a part out of ur time.

You could use the PageSetup.Zoom Property like Worksheets("Sheet1").PageSetup.Zoom = 150 to zoom to a fixed value instead of letting Excel fit it. Or use the PageSetup.FitToPagesWide Property to fit it the width of a page. — Pᴇʜ
Have you checked out the existing questions/answers? This is a pretty common question. — ashleedawg
Hi Peh, thanks for ur answer. I have the following lines in my Pdf export code: .Zoom = 200 .FitToPagesWide = 1 .FitToPagesTall = 0 But still cant help. — Hamouza
Hi @ashleedawg, Yes, I've been looking for it for hours, The only thing I find is to print multiple sheets to pdf, but not a sheet to multiple pdf pages.. Thanks for ur answer tho — Hamouza
@Hamouza you really mean multiple PDF files or just multiple pages? — Pᴇʜ

JC Guidicelli JC Guidicelli · Accepted Answer · 2018-06-06T09:44:54

try to adapt the Orientation and fit the data horizontally, something like this :

With ActiveSheet.PageSetup
    .PrintTitleRows = "$1:$1" 'to repeat your header on each page 
    .CenterHorizontally = True
    .CenterVertically = True
    .Orientation = xlLandscape ' or xlPortrait
    .Zoom = False
    .FitToPagesWide = 1 'fit to wide only
    .FitToPagesTall = 0
End With

Vba: Print an Excel sheet to multiple pdf pages

1 Answers