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

 

[sericom]

That comma at the start is a problem, you'll need to sort it out.
Could be to do with this part:

ReDim ArrSheets(3)
ArrSheets(1) = "TO + Trainee Details"
ArrSheets(2) = "Signatures"
ArrSheets(3) = "Certificate"

Arrays are zero based, so change to this

ReDim ArrSheets(2)
ArrSheets(0) = "TO + Trainee Details"
ArrSheets(1) = "Signatures"
ArrSheets(2) = "Certificate"

 

[sericom]

When you get your array right, the select line will be
ThisWorkbook.Sheets(ArrSheets).Select

 

[The Gunslinger]

That comma at the start is a problem, you'll need to sort it out.
Could be to do with this part:

ReDim ArrSheets(3)
ArrSheets(1) = "TO + Trainee Details"
ArrSheets(2) = "Signatures"
ArrSheets(3) = "Certificate"

Arrays are zero based, so change to this

ReDim ArrSheets(2)
ArrSheets(0) = "TO + Trainee Details"
ArrSheets(1) = "Signatures"
ArrSheets(2) = "Certificate"

that got it, along with the select syntax change you suggested.

Annoyingly, i did originally have that starting at 0, but i was still defining it as 3 and that was what was tripping me up.


I now have another, related, but i don't know how, issue.......

The sheets exported to pdf have some images, and some objects, some of which are printed and some of which aren't, one sheet has some images outside the print area (and this one is not affected) .... for all the other sheets, the images and objects disappear from the excel sheets after running the code, if i switch view from normal to page layout view, then the images are visible, much reduced and in the worng place switch to page break view, and the images and objects are returned to normal, and then when i go back to normal they are all there

running the code, and then saving the workbook and re-opening it, and the images are again returned to their proper positions, and sizes, these images/objects are mixed as i said, in regards to printed or not, and i've tried different properties such as move or don't move with cells, all to no avail.

also, on locked sheets and cells, strange selections are present when the code has been run, cells that are locked and should not be able to be selected, are and not just one cell either, usually a few together