Excel VBA Select worksheets from a list where the value is TRUE

Question

I am attempting to write a macro that will print some sheets to pdf. I have a worksheet ("PDF") that lists all the worksheets in the workbook in column D and next to that in column F I have a list of TRUE/FALSE values. My macro should print all the worksheets in column D that have a TRUE in column F into a single pdf file.

The number of sheets in this workbook does vary.

Below is my first stab at code for this

Sub PDFCreater()

Dim pdfName As String
Dim FullName As String
Dim myArray() As Variant
Dim ArrSize As Integer
Dim ArrWkst As String
Dim RowCnt As Long, ArrCnt As Long

pdfName = Sheets("PDF").Range("D1").Text
FullName = ThisWorkbook.Path & "\" & pdfName & ".pdf"

ReDim myArray(Sheets("PDF").Range("D2").Value)    'Size of array/ number of sheets in PDF
ArrCnt = 0
For RowCnt = 8 To 302
    If Sheets("PDF").Cells(RowCnt, 6).Value = True Then
        myArray(ArrCnt) = Cells(RowCnt, 4).Value
        ArrCnt = ArrCnt + 1
    End If
    RowCnt = RowCnt + 1
Next
'Select all worksheets in MyArray()
Sheets(myArray).Select
ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, Filename:=FullName _
, Quality:=xlQualityMedium, IncludeDocProperties:=False,
IgnorePrintAreas:=False, OpenAfterPublish:=True
End Sub

Storax Storax · Accepted Answer · 2018-05-04T16:28:12

According to your description you have several errors in your code

Sub Test()
Dim pdfName As String
Dim FullName As String
Dim myArray() As Variant
Dim ArrSize As Integer
Dim ArrWkst As String
Dim RowCnt As Long, ArrCnt As Long

    pdfName = Sheets("PDF").Range("D1").Text
    FullName = ThisWorkbook.Path & "\" & pdfName & ".pdf"

    ' You need to re-dim the array in the loop in order to have an array with
    ' the correct dimension. Otherwisae the array is too big and will contain
    ' empty entries
    'ReDim myArray(Sheets("PDF").Range("D2").Value)    'Size of array/ number of sheets in PDF
    ArrCnt = 0
    For RowCnt = 8 To 302
        If Sheets("PDF").Cells(RowCnt, 6).Value Then
            ReDim Preserve myArray(ArrCnt)
            myArray(ArrCnt) = Cells(RowCnt, 4).Value
            ArrCnt = ArrCnt + 1
        End If
        ' the for loop will increase rowcnt itself
        ' no need to do that
        'RowCnt = RowCnt + 1
    Next
    'Select all worksheets in MyArray()
    Sheets(myArray).Select
    ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, Filename:=FullName, _
                                    IncludeDocProperties:=False, _
                                    IgnorePrintAreas:=False, OpenAfterPublish:=True

End Sub

Excel VBA Select worksheets from a list where the value is TRUE

2 Answers