Dynamic print area in vba to print to PDF

F

fpritt24

New Member
Joined
Jul 6, 2017
Messages
8
Hello,

I have a program that creates a dynamic output sheet depending on how many inputs are put into the program. I want to make a program that automatically prints the output sheet into a PDF for the simplicity of the user and that way the document it creates is correctly formatted every time it is run. The code I have as of right now is very barebones as I've never worked with printing into a PDF before. The point I am at right now is the program runs and can print the areas into a PDF but it only prints titlePage range into the PDF. I want titlePage, secondArea, thirdArea, totalPage, notesPage, etc to all be set on separate pages.

Appreciate any help.

(I realize some of this code is unnessecary and can be trimmed but I have other plans for the named ranges later once I get the print function figured out.)

Code: 
Sub PrintPDF()
Dim fileNamee As String
Dim titlePage, totalPage, notesPage, secondArea, thirdArea, fourthArea, fifthArea, sixthArea, seventhArea, eighthArea, ninthArea, tenthArea As Range


'Clear PrintArea
ActiveSheet.PageSetup.PrintArea = False


'Set page ranges to inital values
Set titlePage = Range("A1:H44")
Set secondArea = Nothing
Set thirdArea = Nothing
Set fourthArea = Nothing
Set fifthArea = Nothing
Set sixthArea = Nothing
Set seventhArea = Nothing
Set eighthArea = Nothing
Set ninthArea = Nothing
Set tenthArea = Nothing


'Set inital PrintArea to titlePage
ActiveSheet.PageSetup.PrintArea = titlePage.Address


'Select output pages depending on Input Page
Select Case areaCount
    Case 1
        Set totalPage = Range("A46:H71")
        Set notesPage = Range("A73:H84")
        ActiveSheet.PageSetup.PrintArea = totalPage.Address
        ActiveSheet.PageSetup.PrintArea = notesPage.Address
    Case 2
        Set totalPage = Range("A83:H108")
        Set notesPage = Range("A110:H21")
        Set secondArea = Range("A46:H81")
        ActiveSheet.PageSetup.PrintArea = secondArea.Address
        ActiveSheet.PageSetup.PrintArea = totalPage.Address
        ActiveSheet.PageSetup.PrintArea = notesPage.Address
    Case 3
        Set totalPage = Range("A120:H145")
        Set notesPage = Range("A147:H158")
        Set secondArea = Range("A46:H81")
        Set thirdArea = Range("A83:H118")
        ActiveSheet.PageSetup.PrintArea = secondArea.Address
        ActiveSheet.PageSetup.PrintArea = thirdArea.Address
        ActiveSheet.PageSetup.PrintArea = totalPage.Address
        ActiveSheet.PageSetup.PrintArea = notesPage.Address
    Case 4
        Set totalPage = Range("A157:H182")
        Set notesPage = Range("A184:H195")
        Set secondArea = Range("A46:H81")
        Set thirdArea = Range("A83:H118")
        Set fourthArea = Range("A120:H155")
        ActiveSheet.PageSetup.PrintArea = secondArea.Address
        ActiveSheet.PageSetup.PrintArea = thirdArea.Address
        ActiveSheet.PageSetup.PrintArea = fourthArea.Address
        ActiveSheet.PageSetup.PrintArea = totalPage.Address
        ActiveSheet.PageSetup.PrintArea = notesPage.Address
    Case 5
        Set totalPage = Range("A194:H219")
        Set notesPage = Range("A221:H232")
        Set secondArea = Range("A46:H81")
        Set thirdArea = Range("A83:H118")
        Set fourthArea = Range("A120:H155")
        Set fifthArea = Range("A157:H192")
        ActiveSheet.PageSetup.PrintArea = secondArea.Address
        ActiveSheet.PageSetup.PrintArea = thirdArea.Address
        ActiveSheet.PageSetup.PrintArea = fourthArea.Address
        ActiveSheet.PageSetup.PrintArea = fifthArea.Address
        ActiveSheet.PageSetup.PrintArea = totalPage.Address
        ActiveSheet.PageSetup.PrintArea = notesPage.Address
    Case 6
        Set totalPage = Range("A231:H256")
        Set notesPage = Range("A258:H269")
        Set secondArea = Range("A46:H81")
        Set thirdArea = Range("A83:H118")
        Set fourthArea = Range("A120:H155")
        Set fifthArea = Range("A157:H192")
        Set sixthArea = Range("A194:H229")
        ActiveSheet.PageSetup.PrintArea = secondArea.Address
        ActiveSheet.PageSetup.PrintArea = thirdArea.Address
        ActiveSheet.PageSetup.PrintArea = fourthArea.Address
        ActiveSheet.PageSetup.PrintArea = fifthArea.Address
        ActiveSheet.PageSetup.PrintArea = sixthArea.Address
        ActiveSheet.PageSetup.PrintArea = totalPage.Address
        ActiveSheet.PageSetup.PrintArea = notesPage.Address
    Case 7
        Set totalPage = Range("A268:H293")
        Set notesPage = Range("A295:H306")
        Set secondArea = Range("A46:H81")
        Set thirdArea = Range("A83:H118")
        Set fourthArea = Range("A120:H155")
        Set fifthArea = Range("A157:H192")
        Set sixthArea = Range("A194:H229")
        Set seventhArea = Range("A231:H266")
        ActiveSheet.PageSetup.PrintArea = secondArea.Address
        ActiveSheet.PageSetup.PrintArea = thirdArea.Address
        ActiveSheet.PageSetup.PrintArea = fourthArea.Address
        ActiveSheet.PageSetup.PrintArea = fifthArea.Address
        ActiveSheet.PageSetup.PrintArea = sixthArea.Address
        ActiveSheet.PageSetup.PrintArea = seventhArea.Address
        ActiveSheet.PageSetup.PrintArea = totalPage.Address
        ActiveSheet.PageSetup.PrintArea = notesPage.Address
    Case 8
        Set totalPage = Range("A305:H330")
        Set notesPage = Range("A332:H343")
        Set secondArea = Range("A46:H81")
        Set thirdArea = Range("A83:H118")
        Set fourthArea = Range("A120:H155")
        Set fifthArea = Range("A157:H192")
        Set sixthArea = Range("A194:H229")
        Set seventhArea = Range("A231:H266")
        Set eighthArea = Range("A268:H303")
        ActiveSheet.PageSetup.PrintArea = secondArea.Address
        ActiveSheet.PageSetup.PrintArea = thirdArea.Address
        ActiveSheet.PageSetup.PrintArea = fourthArea.Address
        ActiveSheet.PageSetup.PrintArea = fifthArea.Address
        ActiveSheet.PageSetup.PrintArea = sixthArea.Address
        ActiveSheet.PageSetup.PrintArea = seventhArea.Address
        ActiveSheet.PageSetup.PrintArea = eighthArea.Address
        ActiveSheet.PageSetup.PrintArea = totalPage.Address
        ActiveSheet.PageSetup.PrintArea = notesPage.Address
    Case 9
        Set totalPage = Range("A342:H367")
        Set notesPage = Range("A369:H380")
        Set secondArea = Range("A46:H81")
        Set thirdArea = Range("A83:H118")
        Set fourthArea = Range("A120:H155")
        Set fifthArea = Range("A157:H192")
        Set sixthArea = Range("A194:H229")
        Set seventhArea = Range("A231:H266")
        Set eighthArea = Range("A268:H303")
        Set ninthArea = Range("A305:H340")
        ActiveSheet.PageSetup.PrintArea = secondArea.Address
        ActiveSheet.PageSetup.PrintArea = thirdArea.Address
        ActiveSheet.PageSetup.PrintArea = fourthArea.Address
        ActiveSheet.PageSetup.PrintArea = fifthArea.Address
        ActiveSheet.PageSetup.PrintArea = sixthArea.Address
        ActiveSheet.PageSetup.PrintArea = seventhArea.Address
        ActiveSheet.PageSetup.PrintArea = eighthArea.Address
        ActiveSheet.PageSetup.PrintArea = ninthArea.Address
        ActiveSheet.PageSetup.PrintArea = totalPage.Address
        ActiveSheet.PageSetup.PrintArea = notesPage.Address
    Case 10
        Set totalPage = Range("A379:H404")
        Set notesPage = Range("A406:H417")
        Set secondArea = Range("A46:H81")
        Set thirdArea = Range("A83:H118")
        Set fourthArea = Range("A120:H155")
        Set fifthArea = Range("A157:H192")
        Set sixthArea = Range("A194:H229")
        Set seventhArea = Range("A231:H266")
        Set eighthArea = Range("A268:H303")
        Set ninthArea = Range("A305:H340")
        Set tenthArea = Range("A342:H377")
        ActiveSheet.PageSetup.PrintArea = secondArea.Address
        ActiveSheet.PageSetup.PrintArea = thirdArea.Address
        ActiveSheet.PageSetup.PrintArea = fourthArea.Address
        ActiveSheet.PageSetup.PrintArea = fifthArea.Address
        ActiveSheet.PageSetup.PrintArea = sixthArea.Address
        ActiveSheet.PageSetup.PrintArea = seventhArea.Address
        ActiveSheet.PageSetup.PrintArea = eighthArea.Address
        ActiveSheet.PageSetup.PrintArea = ninthArea.Address
        ActiveSheet.PageSetup.PrintArea = tenthArea.Address
        ActiveSheet.PageSetup.PrintArea = totalPage.Address
        ActiveSheet.PageSetup.PrintArea = notesPage.Address
End Select


'Print to PDF
ActiveSheet.ExportAsFixedFormat _
    Type:=xlTypePDF, _
    fileName:=ActiveWorkbook.Path & "\Output.pdf", _
    Quality:=xlQualityStandard, _
    IncludeDocProperties:=False, _
    IgnorePrintAreas:=False, _
    OpenAfterPublish:=True
End Sub
 

Excel Facts

Copy a format multiple times
Select a formatted range. Double-click the Format Painter (left side of Home tab). You can paste formatting multiple times. Esc to stop
Sort by date Sort by votes
I found a solution to my own problem with the PageBreaks function and just printing the entire worksheet instead of working with PrintArea. I wish excel would put more options into PrintArea within VBA. Here's what I did with it so far:
Code: 
Sub PrintPDF()
Dim fileNamee As String
Dim titlePage, totalPage, notesPage, secondArea, thirdArea, fourthArea, fifthArea, sixthArea, seventhArea, eighthArea, ninthArea, tenthArea As Range


'Clear PrintArea and Page Breaks
ActiveSheet.PageSetup.PrintArea = False
ActiveSheet.ResetAllPageBreaks


'Set page ranges to inital values
Set titlePage = Range("A1:H44")
Set secondArea = Nothing
Set thirdArea = Nothing
Set fourthArea = Nothing
Set fifthArea = Nothing
Set sixthArea = Nothing
Set seventhArea = Nothing
Set eighthArea = Nothing
Set ninthArea = Nothing
Set tenthArea = Nothing


'Set inital PrintArea to titlePage
'ActiveSheet.PageSetup.PrintArea = titlePage.Address
With ActiveSheet
    .HPageBreaks.Add .Range("A45:H45")
End With


'Select output pages depending on Input Page
Select Case Range("E6")
    Case 1
        Set totalPage = Range("A46:H71")
        Set notesPage = Range("A73:H84")
        With ActiveSheet
            .HPageBreaks.Add .Range("A72:H72")
        End With
    Case 2
        Set totalPage = Range("A83:H108")
        Set notesPage = Range("A110:H121")
        Set secondArea = Range("A46:H81")
        With ActiveSheet
            .HPageBreaks.Add .Range("A82:H82")
        End With
        With ActiveSheet
            .HPageBreaks.Add .Range("A109:H109")
        End With
    Case 3
        Set totalPage = Range("A120:H145")
        Set notesPage = Range("A147:H158")
        Set secondArea = Range("A46:H81")
        Set thirdArea = Range("A83:H118")
        With ActiveSheet
            .HPageBreaks.Add .Range("A82:H82")
        End With
        With ActiveSheet
            .HPageBreaks.Add .Range("A119:H119")
        End With
        With ActiveSheet
            .HPageBreaks.Add .Range("A146:H146")
        End With
    Case 4
        Set totalPage = Range("A157:H182")
        Set notesPage = Range("A184:H195")
        Set secondArea = Range("A46:H81")
        Set thirdArea = Range("A83:H118")
        Set fourthArea = Range("A120:H155")
        With ActiveSheet
            .HPageBreaks.Add .Range("A82:H82")
        End With
        With ActiveSheet
            .HPageBreaks.Add .Range("A119:H119")
        End With
        With ActiveSheet
            .HPageBreaks.Add .Range("A156:H156")
        End With
        With ActiveSheet
            .HPageBreaks.Add .Range("A183:H183")
        End With
    Case 5
        Set totalPage = Range("A194:H219")
        Set notesPage = Range("A221:H232")
        Set secondArea = Range("A46:H81")
        Set thirdArea = Range("A83:H118")
        Set fourthArea = Range("A120:H155")
        Set fifthArea = Range("A157:H192")
        With ActiveSheet
            .HPageBreaks.Add .Range("A82:H82")
        End With
        With ActiveSheet
            .HPageBreaks.Add .Range("A119:H119")
        End With
        With ActiveSheet
            .HPageBreaks.Add .Range("A156:H156")
        End With
        With ActiveSheet
            .HPageBreaks.Add .Range("A193:H193")
        End With
        With ActiveSheet
            .HPageBreaks.Add .Range("A220:H220")
        End With
    Case 6
        Set totalPage = Range("A231:H256")
        Set notesPage = Range("A258:H269")
        Set secondArea = Range("A46:H81")
        Set thirdArea = Range("A83:H118")
        Set fourthArea = Range("A120:H155")
        Set fifthArea = Range("A157:H192")
        Set sixthArea = Range("A194:H229")
        With ActiveSheet
            .HPageBreaks.Add .Range("A82:H82")
        End With
        With ActiveSheet
            .HPageBreaks.Add .Range("A119:H119")
        End With
        With ActiveSheet
            .HPageBreaks.Add .Range("A156:H156")
        End With
        With ActiveSheet
            .HPageBreaks.Add .Range("A193:H193")
        End With
        With ActiveSheet
            .HPageBreaks.Add .Range("A230:H230")
        End With
        With ActiveSheet
            .HPageBreaks.Add .Range("A257:H257")
        End With
    Case 7
        Set totalPage = Range("A268:H293")
        Set notesPage = Range("A295:H306")
        Set secondArea = Range("A46:H81")
        Set thirdArea = Range("A83:H118")
        Set fourthArea = Range("A120:H155")
        Set fifthArea = Range("A157:H192")
        Set sixthArea = Range("A194:H229")
        Set seventhArea = Range("A231:H266")
        With ActiveSheet
            .HPageBreaks.Add .Range("A82:H82")
        End With
        With ActiveSheet
            .HPageBreaks.Add .Range("A119:H119")
        End With
        With ActiveSheet
            .HPageBreaks.Add .Range("A156:H156")
        End With
        With ActiveSheet
            .HPageBreaks.Add .Range("A193:H193")
        End With
        With ActiveSheet
            .HPageBreaks.Add .Range("A230:H230")
        End With
        With ActiveSheet
            .HPageBreaks.Add .Range("A267:H267")
        End With
        With ActiveSheet
            .HPageBreaks.Add .Range("A294:H294")
        End With
    Case 8
        Set totalPage = Range("A305:H330")
        Set notesPage = Range("A332:H343")
        Set secondArea = Range("A46:H81")
        Set thirdArea = Range("A83:H118")
        Set fourthArea = Range("A120:H155")
        Set fifthArea = Range("A157:H192")
        Set sixthArea = Range("A194:H229")
        Set seventhArea = Range("A231:H266")
        Set eighthArea = Range("A268:H303")
        With ActiveSheet
            .HPageBreaks.Add .Range("A82:H82")
        End With
        With ActiveSheet
            .HPageBreaks.Add .Range("A119:H119")
        End With
        With ActiveSheet
            .HPageBreaks.Add .Range("A82:H82")
        End With
        With ActiveSheet
            .HPageBreaks.Add .Range("A119:H119")
        End With
        With ActiveSheet
            .HPageBreaks.Add .Range("A156:H156")
        End With
        With ActiveSheet
            .HPageBreaks.Add .Range("A193:H193")
        End With
        With ActiveSheet
            .HPageBreaks.Add .Range("A230:H230")
        End With
        With ActiveSheet
            .HPageBreaks.Add .Range("A267:H267")
        End With
        With ActiveSheet
            .HPageBreaks.Add .Range("A304:H304")
        End With
        With ActiveSheet
            .HPageBreaks.Add .Range("A331:H331")
        End With
    Case 9
        Set totalPage = Range("A342:H367")
        Set notesPage = Range("A369:H380")
        Set secondArea = Range("A46:H81")
        Set thirdArea = Range("A83:H118")
        Set fourthArea = Range("A120:H155")
        Set fifthArea = Range("A157:H192")
        Set sixthArea = Range("A194:H229")
        Set seventhArea = Range("A231:H266")
        Set eighthArea = Range("A268:H303")
        Set ninthArea = Range("A305:H340")
        With ActiveSheet
            .HPageBreaks.Add .Range("A82:H82")
        End With
        With ActiveSheet
            .HPageBreaks.Add .Range("A119:H119")
        End With
        With ActiveSheet
            .HPageBreaks.Add .Range("A156:H156")
        End With
        With ActiveSheet
            .HPageBreaks.Add .Range("A193:H193")
        End With
        With ActiveSheet
            .HPageBreaks.Add .Range("A230:H230")
        End With
        With ActiveSheet
            .HPageBreaks.Add .Range("A267:H267")
        End With
        With ActiveSheet
            .HPageBreaks.Add .Range("A304:H304")
        End With
        With ActiveSheet
            .HPageBreaks.Add .Range("A341:H341")
        End With
        With ActiveSheet
            .HPageBreaks.Add .Range("A368:H368")
        End With
    Case 10
        Set totalPage = Range("A379:H404")
        Set notesPage = Range("A406:H417")
        Set secondArea = Range("A46:H81")
        Set thirdArea = Range("A83:H118")
        Set fourthArea = Range("A120:H155")
        Set fifthArea = Range("A157:H192")
        Set sixthArea = Range("A194:H229")
        Set seventhArea = Range("A231:H266")
        Set eighthArea = Range("A268:H303")
        Set ninthArea = Range("A305:H340")
        Set tenthArea = Range("A342:H377")
        With ActiveSheet
            .HPageBreaks.Add .Range("A82:H82")
        End With
        With ActiveSheet
            .HPageBreaks.Add .Range("A119:H119")
        End With
        With ActiveSheet
            .HPageBreaks.Add .Range("A156:H156")
        End With
        With ActiveSheet
            .HPageBreaks.Add .Range("A193:H193")
        End With
        With ActiveSheet
            .HPageBreaks.Add .Range("A230:H230")
        End With
        With ActiveSheet
            .HPageBreaks.Add .Range("A267:H267")
        End With
        With ActiveSheet
            .HPageBreaks.Add .Range("A304:H304")
        End With
        With ActiveSheet
            .HPageBreaks.Add .Range("A341:H341")
        End With
        With ActiveSheet
            .HPageBreaks.Add .Range("A378:H378")
        End With
        With ActiveSheet
            .HPageBreaks.Add .Range("A405:H405")
        End With
End Select


'Print to PDF
ActiveSheet.ExportAsFixedFormat _
    Type:=xlTypePDF, _
    fileName:=ActiveWorkbook.Path & "\Output.pdf", _
    Quality:=xlQualityStandard, _
    IncludeDocProperties:=False, _
    IgnorePrintAreas:=True, _
    OpenAfterPublish:=True


End Sub
 
Upvote 0
Just one minor note for you.

This does not do what you think it does:
Code: 
Dim titlePage, totalPage, notesPage, secondArea, thirdArea, fourthArea, fifthArea, sixthArea, seventhArea, eighthArea, ninthArea, tenthArea As Range
It only declares the tenthArea variable as Range. The rest are treated as Variant.
You need to have the words " As Range" after each one individually if you want them all to be explicitly declared as Ranges.
 
Upvote 0
Joe4 said:
Just one minor note for you.

This does not do what you think it does:
Code: 
Dim titlePage, totalPage, notesPage, secondArea, thirdArea, fourthArea, fifthArea, sixthArea, seventhArea, eighthArea, ninthArea, tenthArea As Range
It only declares the tenthArea variable as Range. The rest are treated as Variant.
You need to have the words " As Range" after each one individually if you want them all to be explicitly declared as Ranges.
Click to expand...

For real?! lol I thought I read somewhere that it does. Guess I'll need to update a lot of code for it to properly work.
 
Upvote 0
Most of your things SHOULD still work properly, as it is not required to declare your variables at all (though it is considered good practice to do so).
Basically, Variant covers all things (including Range). What declaring each variable helps ensure is that you do not use it in incorrectly (i.e. cannot assign text to a range variables) and helps limit the memory it uses (so it is more efficient).

If you also include "Option Explicit" at the very top of your code, it will force you to declare all variables before using them.
This is a great help in error debugging, as it helps find typos in variable names (it will give you an error when you try to run it telling you that you have an undeclared variable name).
 
Upvote 0
You must log in or register to reply here.

Forum statistics

Threads
1,215,427
Messages
6,124,830
Members
449,190
Latest member
rscraig11

Share this page

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