Page 2 of 2 FirstFirst 12
Results 11 to 12 of 12

Thread: Print Macro as one PDF file
Thanks Thanks: 0 Likes Likes: 0

  1. #11
    New Member
    Join Date
    Aug 2012
    Posts
    24
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Print Macro as one PDF file

    Thanks Yongle for your responses! I will try to determine which method will work best and give these a try.
    Last edited by theanchorman; Jun 7th, 2019 at 07:45 AM.

  2. #12
    New Member
    Join Date
    Aug 2012
    Posts
    24
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Print Macro as one PDF file

    Quote Originally Posted by Yongle View Post
    COMBINING RANGES INTO A SINGLE SHEET WHICH IS PRINTED TO SINGLE PDF
    - method appropriate for ranges sharing common header rows etc

    Code:
    Sub Test()
    'variables
        Const path = "C:\Test\PDF\"     'end with \
        Dim nm  As Variant
        Dim r   As Long
        Dim sh  As Worksheet:       Set sh = ActiveSheet
    'create combo sheet with headers
        Dim ws  As Worksheet:       Set ws = Sheets.Add
        sh.Rows(1).Copy ws.Cells(1)
        ws.PageSetup.PrintTitleRows = "$1:$1"
    'loop named ranges
        For Each nm In Array("smtestp1A", "smtestp1B", "smtestp1C")
            r = ws.UsedRange.Rows.Count + 1
            If r <> 2 Then
                ws.Rows(r).PageBreak = xlPageBreakManual
                r = r + 1
            End If
            sh.Range(nm).Copy ws.Cells(r, 1)
        Next nm
    'save to PDF
        ws.ExportAsFixedFormat Type:=xlTypePDF, Filename:=path & "YourFileName" & ".pdf"
    End Sub

    With worksheet laid out as below, and named ranges specified as per coloured cells, the above code created a single PDF with one page for each named range

    Excel 2016 (Windows) 32 bit
    [COLOR=#FFFFFF ]A[/COLOR]
    [COLOR=#FFFFFF ]B[/COLOR]
    [COLOR=#FFFFFF ]C[/COLOR]
    [COLOR=#FFFFFF ]D[/COLOR]
    [COLOR=#FFFFFF ]E[/COLOR]
    [COLOR=#FFFFFF ]1[/COLOR]
    Header1 Header2 Header3
    [COLOR=#FFFFFF ]2[/COLOR]
    [COLOR=#FFFFFF ]3[/COLOR]
    [COLOR=#FFFFFF ]4[/COLOR]
    a01 b01 c01 smtestp1A
    [COLOR=#FFFFFF ]5[/COLOR]
    a02 b02 c02
    [COLOR=#FFFFFF ]6[/COLOR]
    a03 b03 c03
    [COLOR=#FFFFFF ]7[/COLOR]
    a04 b04 c04
    [COLOR=#FFFFFF ]8[/COLOR]
    a05 b05 c05
    [COLOR=#FFFFFF ]9[/COLOR]
    a06 b06 c06
    [COLOR=#FFFFFF ]10[/COLOR]
    [COLOR=#FFFFFF ]11[/COLOR]
    [COLOR=#FFFFFF ]12[/COLOR]
    [COLOR=#FFFFFF ]13[/COLOR]
    a11 b11 c11 smtestp1B
    [COLOR=#FFFFFF ]14[/COLOR]
    a12 b12 c12
    [COLOR=#FFFFFF ]15[/COLOR]
    a13 b13 c13
    [COLOR=#FFFFFF ]16[/COLOR]
    a14 b14 c14
    [COLOR=#FFFFFF ]17[/COLOR]
    a15 b15 c15
    [COLOR=#FFFFFF ]18[/COLOR]
    a16 b16 c16
    [COLOR=#FFFFFF ]19[/COLOR]
    [COLOR=#FFFFFF ]20[/COLOR]
    [COLOR=#FFFFFF ]21[/COLOR]
    [COLOR=#FFFFFF ]22[/COLOR]
    a21 b22 c22 smtestp1C
    [COLOR=#FFFFFF ]23[/COLOR]
    a22 b23 c23
    [COLOR=#FFFFFF ]24[/COLOR]
    a23 b24 c24
    [COLOR=#FFFFFF ]25[/COLOR]
    a24 b25 c25
    [COLOR=#FFFFFF ]26[/COLOR]
    a25 b26 c26
    [COLOR=#FFFFFF ]27[/COLOR]
    a26 b27 c27
    [COLOR=#FFFFFF ]28[/COLOR]
    [COLOR=#FFFFFF ]29[/COLOR]
    Sheet: Data

    How can I rewrite the code above listed as "ws.PageSetup.PrintTitleRows = "$1:$1"" with the specific ranges such as my previous code had as shown below? Instead of the first row, I want it to use my specific selected ranges as rows and columns.

    With ActiveSheet.PageSetup
    .PrintTitleRows = "inbrdrow"
    .PrintTitleColumns = "inbrdcol"
    End With

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •