Results 1 to 7 of 7

Thread: Reorder pages when exporting as PDF

  1. #1

    Reorder pages when exporting as PDF



    Register for a FREE account, and/
    or Log in to avoid these ads!

    Good morning,

    I am using a VBA macro to export two worksheets as one PDF. The only issue with the code shown below is that because the sheet "take-off" is before "summ sht" in the workbook, the exported PDF also has them in that order. I would like "summ sht" to be page one in the PDF.

    I tried to select the sheets in the order I want them to be, but it did not fix the problem.

    Code:
    Sheets(Array("summ sht", "take-off")).Select
    ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, Filename:= _
    "\\mainserver\common\ben\Exports\" & Sheets("Take-Off").Range("AY2") & " - " & Sheets("take-off").Range("D1") & ".pdf", Quality:= _
    xlQualityStandard, includedocproperties:=False, ignoreprintareas:=False, _
    openafterpublish:=False
    Thanks

  2. #2
    Administrator Ken Puls's Avatar
    Join Date
    Mar 2011
    Location
    Nanaimo, BC, Canada
    Posts
    2,090
    Articles
    79
    Blog Entries
    14
    Hi BEichner, and welcome to the forum!

    This is strange. I mocked up a workbook in Excel 2010, and I can't replicate the issue. Can you upload a copy of the workbook here? Or, if the data is too sensitive for the fourm, but you're comfortable having me look at it, email me a copy?
    Ken Puls, FCPA, FCMA, MS MVP (Excel)

    Master your data with Power Query: Purchase your copy of my book M is for Data Monkey today!

    Main Site: http://www.excelguru.ca -||- Blog: http://www.excelguru.ca/blog -||- Forums: http://www.excelguru.ca/forums
    Check out the Excelguru Facebook Fan Page -||- Follow Me on Twitter

    If you've been given VBA code (a macro) for your solution, but don't know where to put it, CLICK HERE.

  3. #3
    I am using Excel 2007, do you think that is the issue? By not replicating the issue do you mean that you copied the code in and it ordered the PDF as desired?

    I will remove the sensitive material from my workbook and upload it.

    Thanks again.

  4. #4
    Administrator Ken Puls's Avatar
    Join Date
    Mar 2011
    Location
    Nanaimo, BC, Canada
    Posts
    2,090
    Articles
    79
    Blog Entries
    14
    I'm not sure on the 2007, I'll have to test that a bit later.

    With regards to replication, yes. I copied your code, adjusted the file paths, named my sheets the same as yours and they appeared in the PDF in the correct order.
    Ken Puls, FCPA, FCMA, MS MVP (Excel)

    Master your data with Power Query: Purchase your copy of my book M is for Data Monkey today!

    Main Site: http://www.excelguru.ca -||- Blog: http://www.excelguru.ca/blog -||- Forums: http://www.excelguru.ca/forums
    Check out the Excelguru Facebook Fan Page -||- Follow Me on Twitter

    If you've been given VBA code (a macro) for your solution, but don't know where to put it, CLICK HERE.

  5. #5
    I have attached the file.


    PDFpageorder.xlsm

  6. #6
    Administrator Ken Puls's Avatar
    Join Date
    Mar 2011
    Location
    Nanaimo, BC, Canada
    Posts
    2,090
    Articles
    79
    Blog Entries
    14
    Ah... it seems that the worksheet needs to be in the correct order in the user interface. We can work around that by moving the sheets around then back in your macro.

    Try this:

    Code:
    Sub ButtonMacro()
    Dim sPath As String
    Dim lSummSht As Long
    Dim wsSummary As Worksheet
    Dim wsTakeOff As Worksheet
    'set directory here
    sPath = "\\mainserver\common\ben\Exports\"
    'run macro update, hide columns
    Call Update
    Call Hide_Columns
    'move summary sheet into correct place for printing
    Set wsSummary = Worksheets("summ sht")
    Set wsTakeOff = Worksheets("take-off")
    lSummSht = wsSummary.Index + 1
    wsSummary.Move before:=wsTakeOff
    'export take-off and summary sheet as pdf
    Sheets(Array("summ sht", "take-off")).Select
    ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, Filename:= _
    sPath & wsTakeOff.Range("AY2") & " - " & wsTakeOff.Range("D1") & ".pdf", Quality:= _
    xlQualityStandard, includedocproperties:=False, ignoreprintareas:=False, _
    openafterpublish:=False
    'move summary back
    wsSummary.Move before:=Sheets(lSummSht)
    'save the workbook
    ActiveWorkbook.Save
    End Sub
    Ken Puls, FCPA, FCMA, MS MVP (Excel)

    Master your data with Power Query: Purchase your copy of my book M is for Data Monkey today!

    Main Site: http://www.excelguru.ca -||- Blog: http://www.excelguru.ca/blog -||- Forums: http://www.excelguru.ca/forums
    Check out the Excelguru Facebook Fan Page -||- Follow Me on Twitter

    If you've been given VBA code (a macro) for your solution, but don't know where to put it, CLICK HERE.

  7. #7
    Excellent.

    Thank you very much!

Posting Permissions

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