How to export a specific page from a single sheet to pdf?

Question

I am trying to add a button where the button prompts user to enter a page number FROM AND TO and save that specific page to pdf.

Here is the code that I use to save as pdf.

'export to PDF if a folder was selected
If myFile <> "False" Then
    wsA.ExportAsFixedFormat _
        Type:=xlTypePDF, _
        Filename:=myFile, _
        Quality:=xlQualityStandard, _
        IncludeDocProperties:=True, _
        IgnorePrintAreas:=False, _
        **From:=2, To:=2**, _
        OpenAfterPublish:=True
    'confirmation message with file info
    MsgBox "PDF file has been saved."
End If

Possible duplicate of [Excel VBA to Export Selected Sheets to PDF](https://stackoverflow.com/questions/20750854/excel-vba-to-export-selected-sheets-to-pdf) — FAB, Jun 02 '19 at 06:48
Maybe examine the PageBreaks on the sheet to determine the range the required page represents, then set PrintRange to that range, print, then restore the original PrintRange — chris neilsen, Jun 02 '19 at 07:28
Have a try at [this](https://www.mrexcel.com/forum/excel-questions/865330-vba-print-specific-pages.html). — FAB, Jun 02 '19 at 07:32

Abdallah El-Yaddak · Accepted Answer · 2019-06-04T04:44:33.600

My old answer is unnecessary. Maybe I misunderstood your question. ExportAsFixedFormat has "To" & "From" arguments already. So, there is no point in using a custom code to create the same thing.

New Answer:

I am trying to add a button where the button prompts user to enter a page number FROM AND TO and save that specific page to pdf.

All you needed was just a way to ask for the user's input, I guess. In this case, use this code:

Sub AskForPages()
    Dim PageFromStr As String, PageToStr As String, ExportFullName As String
    ExportFullName = ThisWorkbook.Path & "\Test.pdf"
    PageFromStr = InputBox("Insert the number of the first page to export.")
    'Validate the input to be a positive number.
    If IsNumeric(PageFromStr) Then
        If PageFromStr < 1 Then Beep: Exit Sub
    Else
        Beep
        Exit Sub
    End If
    PageToStr = InputBox("Inster the number of the last page to export.")
    'Validate the input to be a number greater than the "From".
    If IsNumeric(PageToStr) Then
        If PageToStr < PageFromStr Then Beep: Exit Sub
    Else
        Beep
        Exit Sub
    End If
    ActiveSheet.ExportAsFixedFormat _
        Type:=xlTypePDF, _
        Filename:=ExportFullName, _
        Quality:=xlQualityStandard, _
        IncludeDocProperties:=True, _
        From:=PageFromStr, To:=PageToStr, _
        IgnorePrintAreas:=False, _
        OpenAfterPublish:=True
End Sub

The old answer:

You can use this sub (passing the page numbers to print (From & To)) to do that:

Sub PrintPages(FromPageNum As Long, ToPageNum As Long, ExportFullName as string)
    Dim Rng As Range, i As Long
    If FromPageNum > ToPageNum Then 'If TO and FROM are mixed, fix them
        i = FromPageNum
        FromPageNum = ToPageNum
        ToPageNum = i
    End If
    Set Rng = GetPageArea(FromPageNum)
    For i = FromPageNum + 1 To ToPageNum
        Set Rng = Union(Rng, GetPageArea(i))
    Next
    Debug.Print Rng.Address
    Rng.ExportAsFixedFormat _
        Type:=xlTypePDF, _
        Filename:=ExportFullName, _
        Quality:=xlQualityStandard, _
        IncludeDocProperties:=True, _
        IgnorePrintAreas:=False, _
        OpenAfterPublish:=True
End Sub

It uses the function GetPageArea which returns the range of the selected page number.

And it needs RndUp function to work, which just rounds up a number.

Function GetPageArea(PageNum As Long, Optional Sh As Worksheet) As Range
    'By Abdallah Ali El-Yaddak
    Dim VBreakMax As Long, HBreakMax As Long, HBreak As Long, VBreak As Long
    Dim c1 As Long, r1 As Long, c2 As Long, r2 As Long
    If Sh Is Nothing Then Set Sh = ActiveSheet
    With Sh
        VBreakMax = .VPageBreaks.Count
        HBreakMax = .HPageBreaks.Count
        If PageNum > (VBreakMax + 1) * (HBreakMax + 1) Then
            Set GetPageArea = Nothing 'Too high page number!
        Else
            If VBreakMax = 0 And HBreakMax = 0 Then
                Set GetPageArea = .UsedRange 'Only one page
            Else
                VBreak = RndUp(PageNum / (HBreakMax + 1))
                HBreak = PageNum - ((VBreak - 1) * (HBreakMax + 1))
                If HBreak = 0 Then
                    HBreak = HBreakMax + 1
                    r2 = .UsedRange.Rows.Count
                    VBreak = VBreak - 1
                Else
                    r2 = .HPageBreaks(HBreak).Location.Row - 1
                End If
                If VBreak > VBreakMax Then
                    c2 = .UsedRange.Columns.Count
                Else
                    c2 = .VPageBreaks(VBreak).Location.Column - 1
                End If
                VBreak = VBreak - 1
                HBreak = HBreak - 1
                If VBreak = 0 Then
                    c1 = 1
                Else
                    c1 = .VPageBreaks(VBreak).Location.Column
                End If
                If HBreak = 0 Then
                    r1 = 1
                Else
                    r1 = .HPageBreaks(HBreak).Location.Row
                End If
                Set GetPageArea = .Range(.Cells(r1, c1), .Cells(r2, c2))
            End If
        End If
    End With
End Function

Function RndUp(Amount As Double, Optional digits As Integer = 0) As Double
    RndUp = Round((Amount + (5 / (10 ^ (digits + 1)))) * (10 ^ digits)) / (10 ^ digits)
End Function

To test, you can try this:

Sub Test()
    PrintPages 3, 5, ThisWorkbook.Path & "\Test.pdf"
End Sub

score -1 · Answer 2 · answered Jun 02 '19 at 07:06

-1

You can try to add these lines:

Dim sheetNbr as integer
sheetNbr = InputBox ("Please input sheet number to export")

Set wsA = wbA.Sheets(sheetNbr)

Now under wsA there should be a reference to the specific worksheet number.

answered Jun 02 '19 at 07:06

Jakub

11

If the sheets are like "Sheet1", "Sheet2", etc... the user will have a wild guess the number is 1, 2, etc. If they are different names, the user will have to count from left to right to get the correct sheet number. – FAB Jun 02 '19 at 07:12
So this will save whole sheet (I mean not worksheet but sheet)..? I want to save only a specific page. – Aroo Jun 02 '19 at 07:14
@Aroo worksheet is the same as sheet, I think. Do you mean that your code saves all worksheets in your file (workbook)? – Jakub Jun 02 '19 at 07:17
ok. in a sheet there are many pages. but I think with your code it will save whole sheet. not single page. – Aroo Jun 02 '19 at 07:20
As far as I know sheet does not contain predefined "pages". However you can also use range.ExportAsFixedFormat - so, if I understand the problem correctly, you can define ranges that contain your pages, then use ExportAsFixedFormat method on these ranges. A screenshot with 'pages' highlighted would be helpful – Jakub Jun 02 '19 at 07:34
You might find this useful as well: https://learn.microsoft.com/en-us/office/vba/api/excel.range.exportasfixedformat – Jakub Jun 02 '19 at 07:43

How to export a specific page from a single sheet to pdf?

2 Answers2