Reorder pages when exporting as PDF

[BEichner]

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.

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

[Ken Puls]

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?

[BEichner]

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.

[Ken Puls]

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.

[BEichner]

I have attached the file.


PDFpageorder.xlsm

[Ken Puls]

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:

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

[BEichner]

Excellent.

Thank you very much!