0

I have an excel file with many sheets. I have created an activex list box to select various sheets and then would like to be able to save as PDF (open up a windows explorer so you can choose a path and put file name). I have the below working to get it to amalgamate the selected for a print preview, but I don't care as much about the print preview, but rather saving the amalgamated sheets as a PDF. Does anyone have a suggestion for changing the last line from printpreview to be able to save?

Sub print_sh()

    Dim i As Long
    Dim c As Long
    Dim SheetArray() As String

    With ActiveSheet.ListBoxChoose
        For i = 0 To .ListCount - 1
            If .Selected(i) Then
                ReDim Preserve SheetArray(c)
                SheetArray(c) = .List(i)
                c = c + 1
            End If
        Next i
    End With

    Sheets(SheetArray()).PrintPreview

End Sub
braX
  • 11,506
  • 5
  • 20
  • 33
Chris
  • 1
  • 1
    You need to `Select` those sheets and then `ActiveSheet.ExportAsFixedFormat`, as the linked duplicate shows. – BigBen May 06 '20 at 18:19
  • Thanks BigBen, this absolutely works. Do you know instead of having filename:= a path, is there a way so it opens up the windows explorer so i can pick a folder for it to save to? – Chris May 06 '20 at 20:58
  • Sure, use [`Application.GetSaveAsFilename`](https://learn.microsoft.com/en-us/office/vba/api/excel.application.getsaveasfilename), or if you just want a folder, see [this question](https://stackoverflow.com/questions/5971292/get-file-path-ends-with-folder). – BigBen May 06 '20 at 21:01

0 Answers0