Macro works standalone but breaks in Personal.xlsb

[Fishboy]

Hi all,

I am hoping one of the gurus can step in and explain something which I cannot get my head around.

I have been helping a fellow user to automate the selecting of all visible sheets in a workbook and piping them out as a PDF in another thread here:

http://www.mrexcel.com/forum/excel-questions/940265-selecting-all-sheets-using-personal-xlsb.html

I have managed to put together the following code which does the job just fine when you manually run the macro:

Sub Export_Visible_Sheets_To_PDF_Original()
' Defines variables
Dim wbk As Workbook
Dim ws As Worksheet
Dim SaveName As String, ArraySheets() As String
Dim x As Variant


' Disable screen updating to reduce flicker
Application.ScreenUpdating = False
     
    With Worksheets(1)
        ' For each open workbook
        For Each wbk In Workbooks
            ' If the workbook name is not the same as the .xlsb name then...
            If wbk.Name <> ThisWorkbook.Name Then
                ' Activate the workbook
                wbk.Activate
                ' Clear the variable ArraySheets
                ReDim ArraySheets(x)
                ' Update variable SaveName as the name of the workbook
                SaveName = Left(wbk.Name, (InStrRev(wbk.Name, ".", -1, vbTextCompare) - 1))
                ' For each sheet in the workbook
                For Each ws In wbk.Worksheets
                    ' If the worksheet is visible then...
                    If ws.Visible = xlSheetVisible Then
                        ' Re-dims the variable ArraySheets preserving any existing sheet names
                        ReDim Preserve ArraySheets(x)
                        ' Add the sheet name to variable ArraySheets
                        ArraySheets(x) = ws.Name
                        ' Increase variable x by 1 for each sheet added
                        x = x + 1
                    End If
                ' Check next worksheet
                Next ws
                ' Select all sheets in ArraySheets
                Sheets(ArraySheets).Select
                ' Export the selected sheets as a single PDF to the specified folder as "SaveName".pdf
                ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, Filename:= _
                "C:\TestFolder\" & SaveName & ".pdf"
            End If
        ' Check next workbook
        Next wbk
    End With
    
' Re-enable screen updating
Application.ScreenUpdating = True


End Sub

So far so good, except for the automation part. The idea is to get it to run automatically whenever another workbook is opened. I utilized my Google-Fu and found this awesome post in another thread on these forums:

http://www.mrexcel.com/forum/excel-...workbook-event-workbook-open.html#post2411684

This prompted me to add the following code to the ThisWorkbook module of Personal.xlsb:

Private WithEvents AppEvents As Application
 
Private Sub Workbook_Open()
 
    Set AppEvents = Application
 
End Sub

Private Sub AppEvents_WorkbookOpen(ByVal Wb As Workbook)
 
    If Not Wb Is Me Then
        Call Export_Visible_Sheets_To_PDF(Wb)
    End If
 
End Sub

Then I also added my original macro to the ThisWorkbook module and amended it as follows:

Private Sub Export_Visible_Sheets_To_PDF(Wb As Workbook)
' Defines variables
Dim wbk As Workbook
Dim ws As Worksheet
Dim SaveName As String, ArraySheets() As String
Dim x As Variant


' Disable screen updating to reduce flicker
Application.ScreenUpdating = False
     
    With Worksheets(1)
        ' For each open workbook
        For Each wbk In Workbooks
            ' If the workbook name is not the same as the .xlsb name then...
            If wbk.Name <> ThisWorkbook.Name Then
                ' Activate the workbook
                wbk.Activate
                ' Clear the variable ArraySheets
                ReDim ArraySheets(x)
                ' Update variable SaveName as the name of the workbook
                SaveName = Left(wbk.Name, (InStrRev(wbk.Name, ".", -1, vbTextCompare) - 1))
                ' For each sheet in the workbook
                For Each ws In wbk.Worksheets
                    ' If the worksheet is visible then...
                    If ws.Visible = xlSheetVisible Then
                        ' Re-dims the variable ArraySheets preserving any existing sheet names
                        ReDim Preserve ArraySheets(x)
                        ' Add the sheet name to variable ArraySheets
                        ArraySheets(x) = ws.Name
                        ' Increase variable x by 1 for each sheet added
                        x = x + 1
                    End If
                ' Check next worksheet
                Next ws
                ' Select all sheets in ArraySheets
                Sheets(ArraySheets).Select
                ' Export the selected sheets as a single PDF to the specified folder as "SaveName".pdf
                ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, Filename:= _
                "C:\TestFolder\" & SaveName & ".pdf"
            End If
        ' Check next workbook
        Next wbk
    End With
    
' Re-enable screen updating
Application.ScreenUpdating = True


End Sub

When another workbook is opened my amended macro is triggered as expected however now for some reason when using it via the open event it breaks on the selecting sheets part...

                ' Select all sheets in ArraySheets
                Sheets(ArraySheets).Select

...with an error "Run-time error '1004': Select methods of Sheets class failed"

Can anyone suggest why it works as a standalone macro, selecting the array of sheet without issue but falls over in the amended version?

Any insight would be greatly appreciated.