Saving multiple selected worksheets from a listbox as PDF

[thochi]

Hi there - reacquainting myself with VBA and I seem to be a bit rusty.

To this point I have been able to successfully write code that will save a constant set of worksheets as a pdf. However, I would now like to alter it to be able to dynamically select the desired worksheets from a list box (I have been able to populate my list box) and then save as a pdf. The last step is where I am have issues. This is what I have thus far..

Dim relativePath As String
Dim Selected As Long


For Selected = 0 To ListBox1.ListCount - 1
If ListBox1.Selected(Selected) = True Then
Sheets("Summary").Range("Q65536").End(xlUp)(2, 1) = ListBox1.List(Selected)
ListBox1.Selected(Selected) = False
End If
Next


relativePath = ThisWorkbook.Path & "\" & Sheets("Title").Range("B28").Value
Sheets (Array(ListBox1.Selected(Selected)))
ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, Filename:=relativePath

End With

Any insight is appreciated!

Thanks,
Thomas

 

[Norie]

Thomas

You'll need to construct an array of the selected sheets.

Dim arrSheets()
Dim relativePath As String
Dim idx As Long    ' don't use Selected, that's a listbox property.:)
Dim cnt As Long

    For idx = 0 To ListBox1.ListCount - 1
        If ListBox1.Selected(idx) Then
            ReDim Preserve arrSheets(cnt)
            arrSheets(cnt) = ListBox1.List(idx)
        End If
    Next idx

    If idx > 0 Then
        relativePath = ThisWorkbook.Path & "\" & Sheets("Title").Range("B28").Value
        Sheets(arrSheets).Select
        ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, Filename:=relativePath
    End If

 

[thochi]

Thanks for the response Norie. But this only seems to PDF the last selected sheet?

 

[Norie]

Does it select the sheets that where selected in the listbox?

 

[thochi]

It seems to yes.

 

[Norie]

Oops, there's actually a couple of typos in the code.

Give this a go.

Dim arrSheets()
Dim relativePath As String
Dim idx As Long    ' don't use Selected, that's a listbox property.:)
Dim cnt As Long

    For idx = 0 To ListBox1.ListCount - 1
        If ListBox1.Selected(idx) Then
            ReDim Preserve arrSheets(cnt)
            arrSheets(cnt) = ListBox1.List(idx)
            cnt = cnt + 1
        End If
    Next idx

    If cnt > 0 Then
        relativePath = "C:\TEST\" & Sheets("Title").Range("B28").Value
        Sheets(arrSheets).Select
        ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, Filename:=relativePath
    End If

 

[thochi]

It works perfectly! Thank you.