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

 

[Micron]

You can't post files here. Some people will download from a file share if you post a shared link and some won't. If the A,B,C...,J in the column is your sheet list and the order you want, I think it would be simpler to put the sheet names in one cell as a comma separated string: A,B,C,...J. IMO it's easier to use VBA SPLIT function to put those values in an array. Then you loop over the array and do the output. I suppose that is what you're after in your first post; I'm just suggesting a different way of dealing with the sheet names and order.

 

[Jmorrison67]

You can't post files here. Some people will download from a file share if you post a shared link and some won't. If the A,B,C...,J in the column is your sheet list and the order you want, I think it would be simpler to put the sheet names in one cell as a comma separated string: A,B,C,...J. IMO it's easier to use VBA SPLIT function to put those values in an array. Then you loop over the array and do the output. I suppose that is what you're after in your first post; I'm just suggesting a different way of dealing with the sheet names and order.

Good morning Micron,

Thanks for your reply. I think this will just do the same as my current result. (The example here is just a simple A.B,C example, but in real world example I have 86 tabs most of which have maximum character title names)

As the worksheets in the workbook have to stay in order A,B,C,D,E...J even if if change the order of the list as per "Revised Order Ref", the macro still prints to pdf in the order they appear in the workbook, not the order as defined in the list. Which I assume will be the same result, if I add them as a string rather than getting it to look at the "Pdf ref" tab for the array of worksheet names?

I need help with the code to somehow get the macro to 1. Reorder the excel sheets based on a list (Revised order Ref) 2. Print them to pdf 3. Put the sheets back in to order in to the workbook?

 

[Georgiboy]

You can try the below which does as you state above:

Sub PDFPackEX()
    Dim i As Long, oary As Variant, ary As Variant, tx As String, q As String
    Dim FolderPath As String, startSheet As Long, SheetNum As Long
    Dim x As Long, y As Long
    
    FolderPath = "R:\XXX\2025\Reporting\PDF PACK EXAMPLE"
    startSheet = 6 ' first sheet of sheets to reorder
    SheetNum = Sheets.Count + 1 - startSheet ' calculated number of sheets to reorder
    
    ' create array of original order
    ReDim oary(1 To SheetNum)
    For x = startSheet To Sheets.Count
        y = y + 1
        oary(y) = Sheets(x).Name
    Next x
    
    ReDim ary(1 To SheetNum)
    For i = 1 To Sheets.Count + 1 - startSheet
        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
    
    ' reorder the sheets to suit list
    For i = UBound(ary) To LBound(ary) Step -1
        Sheets(ary(i)).Move Before:=Sheets(6)
    Next i
    
    Sheets(ary).Select
    
    ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, Filename:=FolderPath & " - " & Format(Now, "dd-mm-yyyy hhmm"), OpenAfterPublish:=False, IgnorePrintAreas:=False
    
    ' reorder sheets back to original
    For i = UBound(oary) To LBound(oary) Step -1
        Sheets(oary(i)).Move Before:=Sheets(6)
    Next i
      
    Call Sheets("Output").Activate

    MsgBox "PDF Pack successfully exported"
End Sub

 

[Jmorrison67]

You can try the below which does as you state above:

Sub PDFPackEX()
    Dim i As Long, oary As Variant, ary As Variant, tx As String, q As String
    Dim FolderPath As String, startSheet As Long, SheetNum As Long
    Dim x As Long, y As Long
   
    FolderPath = "R:\XXX\2025\Reporting\PDF PACK EXAMPLE"
    startSheet = 6 ' first sheet of sheets to reorder
    SheetNum = Sheets.Count + 1 - startSheet ' calculated number of sheets to reorder
   
    ' create array of original order
    ReDim oary(1 To SheetNum)
    For x = startSheet To Sheets.Count
        y = y + 1
        oary(y) = Sheets(x).Name
    Next x
   
    ReDim ary(1 To SheetNum)
    For i = 1 To Sheets.Count + 1 - startSheet
        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
   
    ' reorder the sheets to suit list
    For i = UBound(ary) To LBound(ary) Step -1
        Sheets(ary(i)).Move Before:=Sheets(6)
    Next i
   
    Sheets(ary).Select
   
    ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, Filename:=FolderPath & " - " & Format(Now, "dd-mm-yyyy hhmm"), OpenAfterPublish:=False, IgnorePrintAreas:=False
   
    ' reorder sheets back to original
    For i = UBound(oary) To LBound(oary) Step -1
        Sheets(oary(i)).Move Before:=Sheets(6)
    Next i
     
    Call Sheets("Output").Activate

    MsgBox "PDF Pack successfully exported"
End Sub

Hi GeorgiBoy,

It works in my basic example (above), but when I copy it in to my 'real world' example I get this runtime error:

Any idea what could be causing that?

To get this in to my real-world example - the only things I changed was the 'startSheet' to be = 3 and the list of 38 worksheets on the lookup from tab called "L1 Pack Ref" - all the sheets start from worksheet 3 in my workbook (total sheets altogether is probably over 100 at least).

 

[Georgiboy]

What is the value of 'q' when you get that error?

Another question: You have 100 sheets but you only wish to print a select number of those to PDF?
I ask as the code I set up expects to print all of the sheets but in a different order and not just a select amount of sheets.

 

[Jmorrison67]

What is the value of 'q' when you get that error?

Another question: You have 100 sheets but you only wish to print a select number of those to PDF?
I ask as the code I set up expects to print all of the sheets but in a different order and not just a select amount of sheets.

Ah OK - probably the way I attempted to explain it. Find it tricky still to recreate a "fake" scenario.

'Real world' excel workbook has over 100+ tabs in total, many of them are workings tabs that feed the active 89 tabs. Currently the code I had in my original post the array lookup in real world is a list of 89 tabs names which are in order in the workbook - with the list being on the tab called "Pdf Ref". But management now want to see a different cut of the pack and need to split the 89 tabs over 5 different workbooks without changing the order of the master pack.

In this 'fake' scenario - assume the total tabs is COVER through to J, but the "Packs" will only be a combination of those pages.

Maybe this will help:

Pack 1 - 5 showing different pages that will be run by different macro's - if I can get help with one of the packs, I can change the code to look at the different arrays of tabs that are needed for the packs.

Pack 1:

Pack 2:

Pack 3:

Pack 4, Pack 5 - you get the gist.

Sorry for not explaining it properly

 

[John_w]

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".

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

 

[Georgiboy]

I went in the same direction but instead used a loop to delete the useless sheets in the temp workbook as some users (like me) have Excel set to open with more than one sheet.

Sub PDFPackEX()
    Dim FolderPath As String, rCell As Range, ws As Worksheet
    Dim wbNew As Workbook, wbThis As Workbook, wsPDF As Worksheet
   
    FolderPath = "R:\XXX\2025\Reporting\PDF PACK EXAMPLE"
   
    Set wbThis = ThisWorkbook
    Set wsPDF = wbThis.Sheets("Pdf Ref")
    Set wbNew = Workbooks.Add
   
    For Each rCell In wsPDF.Range("A1", wsPDF.Cells(Rows.Count, "A").End(xlUp))
        If SheetExists(rCell.Value) Then
            wbThis.Sheets(rCell.Value).Copy After:=wbNew.Sheets(wbNew.Sheets.Count)
        Else
            MsgBox "Sheet '" & rCell.Value & "' does not exist!", vbExclamation
            Exit Sub
        End If
    Next rCell
   
    Application.DisplayAlerts = False
    For Each ws In wbNew.Sheets
        If Left(ws.Name, 5) = "Sheet" Then
            ws.Delete
        End If
    Next ws
    Application.DisplayAlerts = True

    wbNew.ExportAsFixedFormat Type:=xlTypePDF, Filename:=FolderPath & " - " & Format(Now, "dd-mm-yyyy hhmm"), OpenAfterPublish:=False, IgnorePrintAreas:=False
    wbNew.Close False
   
    Sheets("Output").Activate
    MsgBox "PDF Pack successfully exported"
End Sub

Function SheetExists(sheetName As String) As Boolean
    Dim ws As Worksheet
    On Error Resume Next
    Set ws = ThisWorkbook.Sheets(sheetName)
    SheetExists = Not ws Is Nothing
    On Error GoTo 0
End Function

 

[John_w]

some users (like me) have Excel set to open with more than one sheet.

With xlWBATWorksheet the new workbook should have only 1 sheet.