Export sheets to single PDF file, but only certain sheets

[The Gunslinger]

As per the title really, i'm looking to export specific sheets from a workbook to a single pdf file, the sheets are 1,3 and 4 plus any sheets found after 7
I've pretty much run myself into a brick wall with this, i simply can't see how to fix it, so i'm hoping someone here will be able to point out where i've screwed up,

I get a "subscript out of range" runtime 9 error on the sheet select line,

Sub Export_to_PDF()

    'On Error GoTo ErrMessage
    Dim xlVer As Integer
    Dim PdfFilename As Variant
    Dim I As Long
    Dim ArrSheets() As string

    ' Disable Screen Updating and Events to speed up the code
    Application.ScreenUpdating = False
    Application.EnableEvents = False
    
    ' Force Password check before continuing
    If Application.InputBox("Password", "Admin Password Required", "", Type:=2) <> Sheet1.Range("Storage_Cell").Value Then GoTo Leave
    
    
    ' First thing to do is check version in use, because below 2007 the PDF function isn't available
    xlVer = Application.Version

    If xlVer < 12 Then    ' v12 = 2007
        MsgBox "You are using a version of Excel which does not support" & vbCrLf & "PDF conversion functions, in order to use this option," & vbCrLf & "please use Excel 2007 or newer.", vbInformation + vbOKOnly, "Option Not Available !"
        GoTo Leave
    End If
    
    ' Now Check if Export to PDF capability is installed (it was an addin for 2007)
    If Not IsPDFLibraryInstalled Then
        ' Show warning message if not installed, as a userform with proper hyperlinks.
        Addin_Required.Show
        GoTo Leave
    End If
    
    
    ' Load SaveAs Dialog, pre-inject file name and path based on this files location
    ' (this potentially can go wrong if invalid filename characters are used in TO Description)
    PdfFilename = Application.GetSaveAsFilename( _
        InitialFileName:=ThisWorkbook.Path & "\" & [Trainee_Rank] & " " & [Trainee_Name] & " (" & [Trg_Obj] & ") Training Record", _
        FileFilter:="PDF, *.pdf", _
        Title:="Export Training Record as PDF")
    
    If PdfFilename <> False Then        ' run export code if filename dialog entry isn't blank or cancelled out of
        ReDim ArrSheets(3)
        ArrSheets(1) = "TO + Trainee Details"
        ArrSheets(2) = "Signatures"
        ArrSheets(3) = "Certificate"
        
        ' Check number of worksheets, sheets after 7 are reports, and need adding to the export selection array
        If ThisWorkbook.Worksheets.Count > 7 Then
            For I = 8 To ThisWorkbook.Worksheets.Count
                ReDim Preserve ArrSheets(UBound(ArrSheets) + 1)
                ArrSheets(UBound(ArrSheets)) = Worksheets(I).Name
            Next I
        End If
        
        ' Select all relevant sheets
        ThisWorkbook.Sheets(Array(ArrSheets)).Select
        
        ' Export selected sheets to single PDF format file
        ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, _
        Filename:=PdfFilename, _
        Quality:=xlQualityStandard, _
        IncludeDocProperties:=False, _
        IgnorePrintAreas:=False, _
        OpenAfterPublish:=False
        
    End If
    
    ' Skip Error Message
    GoTo Leave
    
    
ErrMessage:
    MsgBox "  Something has gone wrong during the export process," & vbCrLf & "  It is unlikely that the Training Record was saved.", vbCritical, "Export/Save Error."
    
Leave:
    ' Re-enable Screen Updating and Event handling
    Application.EnableEvents = True
    Application.ScreenUpdating = True
    
End Sub

 

[JonXL]

Are you trying to select multiple sheets? I'm thinking you can't do that.

Test the code with only one sheet and see if it still hangs there.

 

[sericom]

Your Select line probably should be
ThisWorkbook.Sheets(ArrSheets).Select

 

[The Gunslinger]

EDIT: in reply to you can't multi select....

nope, it's not that, if you record a macro, use ctrl + left cllick to select multiple sheets, then output to pdf, you get this in the recorded output

Sheets(Array("TO + Trainee Details", "Signatures", "Certificate")).Select

i did however find i had a bug in my code, in that i forgot to check for a visible certificate sheet, if it's hidden, then you get a sheet selection 1004 error

 

[The Gunslinger]

Your Select line probably should be
ThisWorkbook.Sheets(ArrSheets).Select

yup, tried that as well, same error

 

[sericom]

While it's in error, put the following in the 'Immediate' window and check you are getting the right sheet names
?join(arrsheets,",")

 

[The Gunslinger]

While it's in error, put the following in the 'Immediate' window and check you are getting the right sheet names
?join(arrsheets,",")

you've lost me there i'm affraid

 

[sericom]

When your code errors and you have a yellow selected line, click the View menu and select 'Immediate Window', then paste the line in down the bottom and hit enter. It will show you the contents of the array.

 

[The Gunslinger]

When your code errors and you have a yellow selected line, click the View menu and select 'Immediate Window', then paste the line in down the bottom and hit enter. It will show you the contents of the array.

ok, thank you, and here is what it returned, on the line directly below that text, copied exactly

,TO + Trainee Details,Signatures,Certificate,Trg Rprt 1,Wkly Rprt 1,Trg Rprt 2

now, ignoring the sheets after Certificate, i added them to make sure it's dynamically adding to the array (they are correct btw)..... what i do see is a random "," at the start of the array, and also, if the select instruction is looking for string entries, separated by commas, then the array in it's native foramt won't be correct..... right ??

 

[The Gunslinger]

working from what you put there in regards to the join comand, i thought i'd try flipping it to a text reference, so tried this.....

ThisWorkbook.Sheets(Array(Mid(Join(ArrSheets, """, """), 4) & """")).Select

playing around with that MID function in outputting to a text variable, and displaying in a msgbox, i was able to manipulate that to return each sheet name, inside the quotes and exactly as the recorded macro displayed it, however, that still didn't work.

As i said, i'm stuffed, i'm out of ideas and clutching at straws, and every **** example i can find is the same useless code that errors, but every palce i find it say "yeah that works" ... yeah right!