VBA PDF Export of Large Spreadsheet

[bifftannen]

Good Day Gentleman,

I am hoping someone could help me out with the problem I am currently having - believe me that it would be beyond appreciated if anyone is willing to offer some of their time to help me with this.

Currently I have a very large spreadsheet(Approx 3080 Rows and with approx 908 columns) that I am working on exporting to PDF in 11x17 landscape mode.

Basically i've worked out how many columns and rows I can fit onto one page in Landscape @ 11x17 and my plan is to just break this document into sections going from top to bottom, and then move onto the next set of columns where I would go top to bottom all the way to the bottom row, then continue on.

Sub DebugPrint()
    
    With ActiveSheet.PageSetup
    .PrintArea = Range("A1:AN3080").Address
    
    .Orientation = xlLandscape
    .FitToPagesWide = 1
    .FitToPagesTall = False
    .PaperSize = xlPaper11x17
    End With
    
    ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, filename:="C:\Test.pdf" _
        , Quality:=xlQualityStandard, IncludeDocProperties:=True, IgnorePrintAreas _
        :=False, OpenAfterPublish:=True
End Sub

Currently the code above works great. It will render 85 pages using columns A to AN on each page while incrementing the rows as the pages go on. So I get a great 11x17 landscape PDF document output as I want.

The problem I am having is I need to somehow move on to range "("AO1:CO3080")" and do the same thing so it builds 85 more pages to the same document with each page using columns AO to CO while incrementing the rows as the pages go on. And then after that i would do range ("CP1:DQ3080") etc, etc, etc.

Is there anyway to do this? I think one way of doing this would be running my function above, writing the PDF file, then changing the range to AO1:CO3080 and appending 85 more pages to that same PDF document, etc, etc. BUT the problem with that is I would need Adobe Acrobat Pro (which i cannot afford) in order to use their API to append documents.

Is there a way to do this using VBA only?

I've attached this quick drawing I made to kind of go along with my explanation (may or may not be helpful - if there is anything you need from me please let me know)

**Many thanks to anyone willing to spare some time to help me out here. Will pay it forward

 

[bifftannen]

Bump

 

[jolivanes]

Does this come close to your requirements?
Change the references as required.
Go for lunch while it is running!!!!!

Sub AAAAA()
Dim i As Long, ii As Long, j As Long, jj As Long, PDF As String
ii = InputBox(Prompt:="How many Columns do you want per Page?.", Title:="Columns per Page.")
jj = InputBox(Prompt:="How many Rows do you want per Page?.", Title:="Rows per Page.")
    For i = 1 To ActiveSheet.UsedRange.Columns.Count Step ii
        For j = 1 To ActiveSheet.UsedRange.Rows.Count Step jj
        ActiveSheet.PageSetup.PrintArea = Range(Cells(j, i), Cells(j + (jj - 1), i + (ii - 1))).Address
            PDF = "C:\Temp_Folder\" & "Column " & i & " To " & i + ii & " Rows " & j & " To " & j + (jj - 1) & ".pdf"    '<----- Change as required
                ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, Filename:=PDF
        Next j
    Next i
End Sub

 

[bifftannen]

Does this come close to your requirements?
Change the references as required.
Go for lunch while it is running!!!!!

Sub AAAAA()
Dim i As Long, ii As Long, j As Long, jj As Long, PDF As String
ii = InputBox(Prompt:="How many Columns do you want per Page?.", Title:="Columns per Page.")
jj = InputBox(Prompt:="How many Rows do you want per Page?.", Title:="Rows per Page.")
    For i = 1 To ActiveSheet.UsedRange.Columns.Count Step ii
        For j = 1 To ActiveSheet.UsedRange.Rows.Count Step jj
        ActiveSheet.PageSetup.PrintArea = Range(Cells(j, i), Cells(j + (jj - 1), i + (ii - 1))).Address
            PDF = "C:\Temp_Folder\" & "Column " & i & " To " & i + ii & " Rows " & j & " To " & j + (jj - 1) & ".pdf"    '<----- Change as required
                ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, Filename:=PDF
        Next j
    Next i
End Sub

Thank you very much sir! Im booting up my Windows Laptop in about 5 minutes and then Ill give it a try and let you know

 

[bifftannen]

Does this come close to your requirements?
Change the references as required.
Go for lunch while it is running!!!!!

Sub AAAAA()
Dim i As Long, ii As Long, j As Long, jj As Long, PDF As String
ii = InputBox(Prompt:="How many Columns do you want per Page?.", Title:="Columns per Page.")
jj = InputBox(Prompt:="How many Rows do you want per Page?.", Title:="Rows per Page.")
    For i = 1 To ActiveSheet.UsedRange.Columns.Count Step ii
        For j = 1 To ActiveSheet.UsedRange.Rows.Count Step jj
        ActiveSheet.PageSetup.PrintArea = Range(Cells(j, i), Cells(j + (jj - 1), i + (ii - 1))).Address
            PDF = "C:\Temp_Folder\" & "Column " & i & " To " & i + ii & " Rows " & j & " To " & j + (jj - 1) & ".pdf"    '<----- Change as required
                ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, Filename:=PDF
        Next j
    Next i
End Sub

Hey Jolivanes,

So I tested the code and I think you are very close. This is exactly what I needed to do BUT instead of all in separate pdf files - i need it to be all the same PDF file. Is there any sort of code that can now combine all of those files? Or is there a way to do exactly what you just did there - but output all of it into the same file? Thanks a lot for taking the time to help!

 

[jolivanes]

Sorry about that.
I misunderstood, or maybe didn't read it right, your question.
Can't you copy the 2nd and 3rd, and maybe more, Range of Rows under one another and print it?

 

[jolivanes]

Are the columns different widths because you mention A to AN, AO to CO and CP to DQ
A to AN = Column 1 to Column 40
AO to CO = Column 41 to Column 93
CP to DQ = Column 94 to Column 121

 

[bifftannen]

No problem!! That would also work and might actually be faster to do one publish to PDF. Any idea how I could do that? I.e copy them all to a new sheet so the original isn’t modified?

Also sorry about the columns. I wasn’t being precise and was just kind of guessing quickly. They are all in fact the same width

 

[jolivanes]

Try this with a copy of your original.
Change references if and where required.

Sub Copy_And_Print()
Dim lr As Long, lc As Long, i As Long, j As Long, PDF As String
lr = Cells(Rows.Count, 1).End(xlUp).Row
lc = Cells(1, Columns.Count).End(xlToLeft).Column
Application.ScreenUpdating = False
j = InputBox(Prompt:="How many Columns do you want per Page?.", Title:="Columns per Page.")
    For i = 1 + j To lc Step j
        Range(Cells(1, i), Cells(lr, i + (j - 1))).Copy Cells(Rows.Count, 1).End(xlUp).Offset(1)
    Next i
        ActiveSheet.PageSetup.PrintArea = Range(Cells(1, 1), Cells(Cells(Rows.Count, 1).End(xlUp).Row, 1)).Resize(, j).Address
            PDF = "C:\Test\" & "Printout" & ".pdf"    '<---- Change as required
                ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, Filename:=PDF
ActiveSheet.Range(Cells(lr + 1, 1), Cells(Cells(Rows.Count, 1).End(xlUp).Row, 1)).Resize(, j).ClearContents
Application.ScreenUpdating = True
End Sub

 

[bifftannen]

Try this with a copy of your original.
Change references if and where required.

Sub Copy_And_Print()
Dim lr As Long, lc As Long, i As Long, j As Long, PDF As String
lr = Cells(Rows.Count, 1).End(xlUp).Row
lc = Cells(1, Columns.Count).End(xlToLeft).Column
Application.ScreenUpdating = False
j = InputBox(Prompt:="How many Columns do you want per Page?.", Title:="Columns per Page.")
    For i = 1 + j To lc Step j
        Range(Cells(1, i), Cells(lr, i + (j - 1))).Copy Cells(Rows.Count, 1).End(xlUp).Offset(1)
    Next i
        ActiveSheet.PageSetup.PrintArea = Range(Cells(1, 1), Cells(Cells(Rows.Count, 1).End(xlUp).Row, 1)).Resize(, j).Address
            PDF = "C:\Test\" & "Printout" & ".pdf"    '<---- Change as required
                ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, Filename:=PDF
ActiveSheet.Range(Cells(lr + 1, 1), Cells(Cells(Rows.Count, 1).End(xlUp).Row, 1)).Resize(, j).ClearContents
Application.ScreenUpdating = True
End Sub

Very close!!

Basically what you did works great - I can fit about 78 columns and 76 rows from my document onto a 11x17 page in Landscape view - so with your example I put in 78 as my number and it outputs 55 pages (amount of pages to get all the way to the bottom of the sheet). BUT what I was hoping to accomplish would be to enter something like "156" columns and have it it generate 110 pages total. 55 of the pages would be columns 1 to 78 top to bottom, and then pages 56 to 105 would be columns 79 to 156. Etc etc.

However when I put in something like 156, it just fits 156 columns onto one page, which when printed just isn't visible/readable.