Save same sheet to PDF multiple times - is it possible?

Prophet4Profit

New Member
Joined
Sep 30, 2019
Messages
3
Hello, I'm a new poster to the forum. I have a question and cannot find the answer anywhere so I thought I would ask here. I would like to know if it is possible to save the same sheet to a PDF in a loop (with different data each time) creating a multi-page PDF. As a test, I have been able to save multiple excel sheets from the same workbook to one PDF but ultimately that is not what I am trying to do.

Here is a little more detail: I have sheet that I import a bunch of data to. I run some calculations on each set of data from what I imported in a loop and at the end of calculating each set of data I want to save the results to a PDF. However, I want each set of data saved to a separate page within the same PDF file.

I have tried to make it work unsuccessfully and am now to the point wondering if it is even possible. Is it, or should I try a different approach? Speed is important because there is the potential to be going through a lot of data creating a lot of pages.

Thanks

Jeff
 

Excel Facts

How to show all formulas in Excel?
Press Ctrl+` to show all formulas. Press it again to toggle back to numbers. The grave accent is often under the tilde on US keyboards.
Hello and thanks for the response. I don't have the code yet - currently I have code to save multiple excel sheets to one PDF. I have been researching whether or not the idea is even possible before attempting to write code. Ideally I imagined I would try to put each sheet iteration into an array and then print the array of sheets (if possible). The other idea is to put each sheet iteration on its own sheet and then print all sheets (which I currently have code for) but there are limitations to the number of sheets in a workbook. Any ideas or suggestions would be greatly appreciated.

Thanks

Jeff
 
Upvote 0
You can create a new book, you can put each data set on a sheet of the new book, that is, if you have 10 data sets, then you will end up with 10 sheets in the new book.
Then you save the entire book as a pdf.


What I don't understand is how you build each data set.
 
Upvote 0
I understand what you are suggesting which is the route I was initially going to take (and may still) - this is the code I have used for combining all worksheets into one PDF (which works):
I was just curios if the way I initially described is even possible or if there is a better/faster way.

Code:
Sub PrintAllSheetsToOnePDF() 'Combine all worksheets into one PDF


Dim strSheets() As String
Dim strfile As String
Dim sh As Worksheet
Dim icount As Integer
Dim myfile As Variant


'Save Sheet names to an Array
For Each sh In ActiveWorkbook.Worksheets
    If sh.Visible = xlSheetVisible Then
        ReDim Preserve strSheets(icount)
        strSheets(icount) = sh.Name
        icount = icount + 1
    End If
Next sh


If icount = 0 Then 'None found error.
    MsgBox "A PDF cannot be created because no sheets were found.", , "No Sheets Found"
Exit Sub
End If


'Prompt for save location
strfile = "Sheets" & "_" _
    & Format(Now(), "yyyymmdd_hhmmss") _
    & ".pdf"


strfile = ThisWorkbook.Path & "\" & strfile


myfile = Application.GetSaveAsFilename _
    (InitialFileName:=strfile, _
    FileFilter:="PDF Files (*.pdf), *.pdf", _
    Title:="Select Folder and File Name to Save as PDF")


If myfile <> "False" Then 'save as PDF
    ThisWorkbook.Sheets(strSheets).Select
    ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, Filename:= _
    myfile, Quality:=xlQualityStandard, IncludeDocProperties:=True, _
    IgnorePrintAreas:=False, OpenAfterPublish:=True
Else
    MsgBox "No File Selected. PDF will not be saved", vbOKOnly, "No File Selected"
End If


End Sub


Thanks

Jeff
 
Upvote 0
In fact, that way you have seems like a good option.
 
Upvote 0
I was just curios if the way I initially described is even possible or if there is a better/faster way.

Jeff

You are constrained by the capabilities of Excel (and the Microsoft VBA.) Neither the MS Export to PDF nor the Print to PDF function in Excel has the ability to Append the file.
You are better off I think with the method you have as the time required for opening and appending a PDF file multiple times is likely far greater than creating the multiple sheets.

Using the Export as PDF is far better in Word, but I don't see any significant difference for Excel. Word allows bookmarks to appear in an Exported PDF.
 
Upvote 0

Forum statistics

Threads
1,223,952
Messages
6,175,596
Members
452,657
Latest member
giadungthienduyen

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top