VBA to export to pdf in a specified order WITHOUT reordering the tabs in the workbook

[Jmorrison67]

Good morning Community ,

I have a problem I was hoping you could help guide me with. Will try to summarize it (i tried to upload an example excel file with what i'm trying to do but the "upload image" function didnt link it - any suggestion on how to get it in here?)

Current solution / process:
- Note: This is an example I created, the real world example has 89 tabs which would be listed out in the array in Col A below (not in the example)
- Macro set up that looks at the tab "Pdf Ref" array in column A for the list and then creates the PDF (goes through a step to check the naming of the tab is the same as in the list, also displays a box for user that it's run successfully).
- The worksheet ordering in the workbook is the same order as the array is asking for it to be printed.
- As the real world example is 89 worksheets, the example should give the same method that i'm looking for help with.

Copy of code used that gives the desired result:

Sub PDFPackEX()

Dim FolderPath As String
FolderPath = "R:\XXX\2025\Reporting\PDF PACK EXAMPLE"

Dim i As Long, ary, tx As String, q As String
ReDim ary(1 To 11)
For i = 1 To 11
    tx = Sheets("Pdf Ref").Range("A" & i).Value
    q = Replace(tx, "'", "''")
    If Not Evaluate("ISREF('" & q & "'!A1)") Then MsgBox "Sheets " & q & " doesn't exist": Exit Sub
    ary(i) = tx
Next

Sheets(ary).Select

 ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, Filename:=FolderPath & " - " & Format(Now, "dd-mm-yyyy hhmm"), OpenAfterPublish:=False, IgnorePrintAreas:=False
    
Call Sheets("Output").Activate

    MsgBox "PDF Pack successfully exported"

End Sub

What I need help with

I need a new code that looks at the same array of worksheets, but has them in a different order for the pdf print as per the array in "Revised Order Ref". Example below:

Note: the order of the worksheets in the excel workbook CANNOT be changed. I have 5 different combinations in the real world of packs which take different tabs out of the 89 tabs and create different pdf's. In this example I have shown one pack (which i will just replicate the code for the other packs i need changing the array numbering).

I'm sure it will be possible to do, but just really need some help with it.

Thanks in advance
Jmorrison67

 

[Georgiboy]

With xlWBATWorksheet the new workbook should have only 1 sheet.

I did not know that, thanks for filling me in.

I had a look through your code, can I ask, what is 'n' doing - is it redundant?

 

[John_w]

'n' is a count/index of the sheets in PDFworkbook. Without it I could have written:

                .Worksheets(PDFsheets(i, 1)).Copy After:=PDFworkbook.Worksheets(PDFworkbook.Worksheets.Count)
                PDFworkbook.Worksheets(PDFworkbook.Worksheets.Count).UsedRange.Value = PDFworkbook.Worksheets(PDFworkbook.Worksheets.Count).UsedRange.Value

 

[Georgiboy]

Ah I see now, not sure why I didn't see it earlier.

 

[Jmorrison67]

Try this macro, which copies the sheets in the required order to a temporary workbook.

Public Sub Save_Sheets_As_PDF()

    Dim outputPDFfile As String
    Dim PDFworkbook As Workbook
    Dim PDFsheets As Variant
    Dim i As Long, n As Long
  
    outputPDFfile = "R:\XXX\2025\Reporting\PDF PACK EXAMPLE - " & Format(Now, "dd-mm-yyyy hhmm") & ".pdf"
  
    Application.ScreenUpdating = False
  
    Set PDFworkbook = Workbooks.Add(xlWBATWorksheet)
  
    With ThisWorkbook
        With .Worksheets("Revised Order Ref")
            PDFsheets = .Range("A1", .Cells(.Rows.Count, "A").End(xlUp)).Value
        End With
        n = 1
        For i = 1 To UBound(PDFsheets)
            If SheetExists(CStr(PDFsheets(i, 1))) Then
                .Worksheets(PDFsheets(i, 1)).Copy After:=PDFworkbook.Worksheets(n)
                n = n + 1
                PDFworkbook.Worksheets(n).UsedRange.Value = PDFworkbook.Worksheets(n).UsedRange.Value
            End If
        Next
    End With
  
    Application.DisplayAlerts = False
    PDFworkbook.Worksheets(1).Delete
    Application.DisplayAlerts = True
    PDFworkbook.ExportAsFixedFormat Type:=xlTypePDF, Filename:=outputPDFfile, Quality:=xlQualityStandard, _
                                    IncludeDocProperties:=True, IgnorePrintAreas:=False, OpenAfterPublish:=True
    PDFworkbook.Close False
  
    Application.ScreenUpdating = True
  
    MsgBox "Created " & outputPDFfile, vbInformation
  
End Sub


Private Function SheetExists(wsName As String, Optional wb As Workbook) As Boolean
    If wb Is Nothing Then Set wb = ThisWorkbook
    On Error Resume Next
    SheetExists = Len(wb.Worksheets(wsName).Name)
    On Error GoTo 0
End Function

Hi John,

Thanks for helping with the code. It works almost perfectly except it seems to put in 2 x blank sheets in to the pdf. For example - the "real world" pdf pack should be 38 pages long, but after i run teh above code it is 40 pages long.
1st blank page appears on Page 5
2nd blank page appears on Page 9

I've checked my workbook / worksheet print area's to make sure the worksheets either side of those pages are within the 1 page print area and everything seems OK. Any idea's?

 

[John_w]

Do you get the blank pages when you print preview those sheets?

 

[Jmorrison67]

Hi John,

Thanks for helping with the code. It works almost perfectly except it seems to put in 2 x blank sheets in to the pdf. For example - the "real world" pdf pack should be 38 pages long, but after i run teh above code it is 40 pages long.
1st blank page appears on Page 5
2nd blank page appears on Page 9

I've checked my workbook / worksheet print area's to make sure the worksheets either side of those pages are within the 1 page print area and everything seems OK. Any idea's?

Do you get the blank pages when you print preview those sheets?

Morning John,
Apologies only just picking up your message now - I was certain I fixed the print area's correctly but now notice what the issue was (my fault) - I've marked your 1st response as the solution.
It is absolutely amazing the power of VBA - Would be interested to know the occupation of all the guru's on here. I'd love to get some advice on how to improve my VBA knowledge and skills and be able to write something like this!
Really appreciate your help & everyone else who responded
Kind Regards
Jmorrison67