Selecting sheets based off of a dynamic range, then exporting

I have an export button in my excel workbook that selects sheets to export to as a joined PDF. I originally had the sheets hard coded. For example: Sheets(Array("Volume Report","Notes","Invoice01")).Select and that worked. I tried to get fancy with it by dynamically building the list from a named range that populates with new sheets as I add them to the workbook.

I am able to build the string and it returns the string value that if manually replaced works but when passed as a variable I get Runtime Error '9': Subscript out of Range Im not sure why manually passing the string works but dynamically assigning it does not. Any help in accomplishing the intended task is appreciated.

The VBA code:

Sub ExportAsPDF()
Dim wb As Workbook
Dim currentSheet As Worksheet
Dim filePath As String
Dim sheetNames As String
Dim cell As Range

Set wb = ActiveWorkbook
Set currentSheet = ActiveSheet

' Build the file path
filePath = wb.Path & "\" & "0_" & Range("Notes_Revision").Value & ") " & Range("Notes_JobName").Value & " - VR - " & Range("Notes_Author").Value & ".pdf"

' Manually build a string of sheet names from Ref_PrintRange
For Each cell In Range("Ref_PrintRange")
    If sheetNames = "" Then
        sheetNames = cell.Value
    Else
        sheetNames = sheetNames & Chr(34) & ", " & Chr(34) & cell.Value
    End If
Next cell

' Select the specified sheets for export
Sheets(Array(sheetNames)).Select

' Export the selected sheets as PDF
ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, Filename:=filePath, _
Quality:=xlQualityStandard, IncludeDocProperties:=True, IgnorePrintAreas:=False, _
OpenAfterPublish:=False

' Reselect the original sheet
currentSheet.Select

End Sub

Thank you in advance.

Solution

Transposing Ref_PrintRange will make it a 1D array that can be used to select a range of worksheets.

[] can be used as a shorthand for Evaluate. Here I use Transpose(Ref_PrintRange)] to convert the values.

Sub ExportAsPDF()
    Dim wb As Workbook
    Dim currentSheet As Worksheet
    Dim filePath As String
   
    Set wb = ActiveWorkbook
    Set currentSheet = ActiveSheet

    ' Build the file path
    filePath = wb.Path & "\" & "0_" & Range("Notes_Revision").Value & ") " & Range("Notes_JobName").Value & " - VR - " & Range("Notes_Author").Value & ".pdf"

    ThisWorkbook.Worksheets([Transpose(Ref_PrintRange)]).Select
    
    ' Export the selected sheets as PDF
    ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, Filename:=filePath, _
        Quality:=xlQualityStandard, IncludeDocProperties:=True, IgnorePrintAreas:=False, _
        OpenAfterPublish:=False

    ' Reselect the original sheet
    currentSheet.Select

End Sub

Addendum

BigBen pointed that I'm assuming that Ref_PrintRange is a column. So for completeness:

If Ref_PrintRange is a column use:

ThisWorkbook.Worksheets([Transpose(Ref_PrintRange)]).Select

If Ref_PrintRange is a row use:

ThisWorkbook.Worksheets([Transpose(Transpose(Ref_PrintRange))]).Select

Microsoft Excel for Microsoft 365 can use TOCOL for row or column data:

ThisWorkbook.Worksheets([Transpose(TOCOL(Ref_PrintRange))]).Select