PDF every named range to individual page - VBA

1
votes

I have an excel model that dynamically changes the number and names of ranges based on input from the user. All named ranges are then populated on a reference tab with their location in a corresponding cell. I need to write a macro that PDFs each of the named ranges on individual pages, but as a singular PDF file.

I have tried all of the forums that I can find that show various ways to PDF ranges, but nothing solves for two particular problems cohesively:

1) The number of named ranges will dynamically change. I cannot statically name them in my VBA code. 2) The named ranges are on 10 separate worksheets. 3) Some of the sheets have multiple named ranges on them. It's not always one-to-one, meaning I need it to PDF multiple named ranges from the same sheet, but the result must still be one named range per PDF'd page.

Can someone help me take the below list and accomplish my goal of creating one PDF file with a singular page for each named range? For reference, my named range table looks like the below:

Named Range, Location

Range1, =Sheet1!$K$2:$R$21
Range2, =Sheet2!$K$2:$R$21
Range3, =Sheet3!$K$2:$R$21
Range4, =Sheet4!$K$2:$R$21
Range5, =Sheet5!$K$2:$R$21
Range6, =Sheet6!$K$2:$R$21
Range7, =Sheet7!$K$2:$R$21
Range8, =Sheet8!$K$2:$R$21
Range9, =Sheet9!$K$2:$R$21
Range10, =Sheet10!$B$2:$I$21
Range10, =Sheet10!$K$2:$R$21

I've attempted multiple angles at this problem. I've attempted to move all named ranges to one tab, but this is very difficult because the named ranges have different formatting, meaning it's not as simple as copy and paste. I'm hesitant to show any code here because the 20 iterations I've tried have come nowhere close to accomplishing my goal. Any guidance structurally on how to do this is VERY VERY appreciated.

The end result I hope for is a macro that PDFs all named ranges from an excel workbook with each named range getting its own page in the PDF file.

2
excelvbapdf
If you set the print area on required each sheet to the named range you should be able to output all the required ranges one per page using something like this: stackoverflow.com/questions/14404650/… The only tricky part might be where you have multiple ranges on the same sheet - you could try making temporary copies of those sheets, with one range set to print per copy. – Tim Williams
...seems like you can set the print area to a multi-area range on a given sheet, and each area gets its own page. So maybe try that. – Tim Williams
Tim - thanks for your help. I am having a lot of trouble where there are multiple ranges on the same sheet. Do you have a good example for using temporary copies of sheets? Apologies, I'm mostly learning these functions on the fly from examples posted on forums. – Matt Rubright
For sheets with multiple ranges I'd try first setting the print area using a union of the two ranges - that should put each range on a separate page. – Tim Williams
Have you seen any examples of unions when there are more than two ranges (I have up to 10 max) on a sheet? The number of ranges on each sheet will change dynamically, so I'm having trouble finding anything that doesn't hardcode in ranges too. – Matt Rubright

2 Answers

1
votes

With All due credit to @Tim Williams idea, i am just offering basic skeleton code as simple example to be modified according to requirement.

Sub test()
Dim Rng As Range
Dim Ws As Worksheet, Wb As Workbook
Dim Nm As Name
Set Wb = Application.Workbooks.Add

    For Each Nm In ThisWorkbook.Names
    Set Rng = Nm.RefersToRange
    Set Ws = Rng.Worksheet
    With Ws.PageSetup
        .PrintArea = Rng.Address
        ' may set other pagesetup properties according to requirement
    End With
    Ws.Copy After:=Wb.Sheets(Wb.Sheets.Count)
    Next Nm

Wb.ExportAsFixedFormat Type:=xlTypePDF, Filename:= _
        "C:\Users\user\Documents\Range to PDF.pdf", Quality:=xlQualityStandard, _
        IncludeDocProperties:=True, IgnorePrintAreas:=False, OpenAfterPublish:= _
        True
Wb.Close False
Set Wb = Nothing
End Sub
1
votes

If you set the print area on required each sheet to the named range you should be able to output all the required ranges one per page using something like this:

Save multiple sheets to .pdf

For cases where you have multiple ranges on a sheet, if you set the printarea to the union'ed total range it looks like each area goes into a separate page, so that should not be a big problem.

Here's a fleshed-out example of how you could do it:

Sub PrintIt()

    Dim dict As Object, rngName As Range, wb As Workbook, nm As String
    Dim shtName As String, rng As Range, prevRange, k, first As Boolean
    Set dict = CreateObject("scripting.dictionary")

    Set wb = ThisWorkbook

    'Start by collecting all of the required ranges, organized by worksheet
    Set rngName = wb.Sheets("Reference").Range("A2") 'first range name
    Do While rngName <> ""
        nm = Trim(rngName.Value)
        Set rng = wb.Names(nm).RefersToRange   'get the range
        shtName = rng.Parent.Name              'sheet name for this range

        If Not dict.exists(shtName) Then   'new sheet?
            dict.Add shtName, rng          'create an entry for this sheet
        Else
            'add this range to the sheet's dictionary entry
            Set prevRange = dict(shtName)
            Set dict(shtName) = Application.Union(prevRange, rng)
        End If
        Set rngName = rngName.Offset(1, 0) 'next name
    Loop

    If dict.Count = 0 Then Exit Sub 'no names

    'set up the printing for each sheet
    first = True
    For Each k In dict.keys
        'set print area (can be multiple areas on one sheet)
        wb.Sheets(k).PageSetup.PrintArea = dict(k).Address
        'select for printing "first" controls whether sheet selection is cumulative
        wb.Sheets(k).Select first
        first = False '<< later sheets now get added to the already-selected sheet
    Next k

    ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, _
        Filename:="C:\tempo.pdf", Quality:=xlQualityStandard, _
        IncludeDocProperties:=True, IgnorePrintAreas:=False, OpenAfterPublish:=True

End Sub