Defining an array using a string variable

[mcomp72]

I'm a moderate VBA coder, meaning not a beginner, but I also wouldn't call myself an expert. I've hit upon a problem I can't figure out.

I am going to be creating a userform with checkboxes, where the user will be able to put a check next to the name of the sheets that he/she wants to include when generating a single PDF file. Since it is unknown which ones they will select, I want to have my code use a string variable to define the array that will select the correct sheets. Here is the code I have written to test it out.

Sub GeneratePDF()

Dim Filename As String
Dim SheetsToPrint As String

Filename = "TempFile"

SheetsToPrint = "Page 2"
SheetsToPrint = SheetsToPrint & ", " & "Page 3"

Sheets(Array(SheetsToPrint)).Select

ActiveSheet.ExportAsFixedFormat xlTypePDF, ThisWorkbook.Path & "/" & Filename & ".pdf", , , False

End Sub

If I remove this line:

SheetsToPrint = SheetsToPrint & ", " & "Page 3"

the sub works, and it generates a PDF of Page 2 only. But with the above line in there, I get a Run-time Error 9: subscript out of range on the line that creates the array. I can't figure out what I'm doing wrong.

I've searched a few forums about this, but haven't had any luck finding a solution. Any help would be greatly appreciated.

 

[mcomp72]

I input a Debug.Print line right before the line creating the Array, to see what "SheetsToPrint" was. This is what I got in the immediate window:

Page 2, Page 3

 

[Peter_SSs]

As the title of your thread states, you need to define an array, not a string. However, you can use the string to then create an array as follows.
Note that I have joined the two sheet names with just a comma, not comma-space, but that was just to simplify the Split (which creates an array) a little.
Anyway, give this a try.

Sub GeneratePDF()

Dim Filename As String
Dim SheetsToPrint As String

Filename = "TempFile"

SheetsToPrint = "Page 2"
SheetsToPrint = SheetsToPrint & "," & "Page 3"

Sheets(Split(SheetsToPrint, ",")).Select

ActiveSheet.ExportAsFixedFormat xlTypePDF, ThisWorkbook.Path & "/" & Filename & ".pdf", , , False

End Sub

 

[mcomp72]

That worked! Thank you very much!!

 

[Peter_SSs]

That worked! Thank you very much!!

No problem - glad to help.