Using an array to specify worksheets to print

Question

I have a workbook from which I want to select certain worksheets to export. This will vary each time and the names of the worksheets appear in cells Y48:Y55 based on whether they are selected above: selected cells and populated list

I would like to use this list to fill a dynamic array which I can call to export to pdf only the sheets in the array.

I have got the array in vba that I can see in a message box, I just can't get it into the Sheets(Array(myarray).select part of the code.

If I type all the values in manually eg. sheets(Array("COVER", "CLASS CHART").Select it will work, but I don't know how to get the individual values out of myarray.

Sorry I'm not a coder but I can't find this anywhere.

So far it looks like this:

    Dim myarray() As String, size As Integer, i As Integer

    size = WorksheetFunction.CountA(Range("y48:y55"))
    ReDim myarray(size)

    For i = 1 To size
        myarray(i) = Cells(i + 47, 25).Value
    Next i
    MsgBox (size)
    MsgBox Join(myarray, vbCrLf)
    Sheets(myarray().Select
    Sheets("COVER").Activate

    answer = MsgBox("Do you want to export to PDF?", vbYesNo + vbQuestion, "Export to PDF")
    If answer = vbYes Then
fileSaveName = Application.GetSaveAsFilename("", fileFilter:="PDF (*.pdf), *.pdf")
        If fileSaveName <> False Then
            ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF,         Filename:=fileSaveName, Quality:=xlQualityStandard, IncludeDocProperties:=True, IgnorePrintAreas:=False, OpenAfterPublish:=True
        End If
    End If
    Sheets("REPORT").Select

End Sub

Kyle Kyle · Accepted Answer · 2016-06-30T21:46:49

Instead of using Sheets(myarray().Select, use a loop:

For i = Lbound(myarray,1) to Ubound(myarray,1)
   Worksheets(myarray(i)).Select False
Next i

The false makes it so it doesn't lose it's previous selection.

Using an array to specify worksheets to print

1 Answers