Selecting sheets for export

Question

I'm trying to create an Excel macro that exports worksheets to PDF. I have a simple piece of code below that successfully exports the active sheet to the folder that I want. What I want to do - and can't find a solution for - is a way to give the user the option to export multiple worksheets to the same PDF. In my application the worksheets exported may have different names, may be created after the macro is written and may be a different number of sheets each time. I have tried to make arrays that use selection but this is beyond my own knowledge of macro writing, which is limited. In an ideal world, I'd like to use a pop-up selection box to choose the sheets to export, but I'll start with the basics of the code first. Could someone please suggest a section of code that would suit my application?

Sub Export_PDF()

'File name
Dim saveName As String
saveName = Left(ActiveWorkbook.Name, InStrRev(ActiveWorkbook.Name, ".")) & "pdf"

'File path
Dim saveLocation As String
saveLocation = "C:\Users\" & Environ("username") & "\Temp Out\"

'Save Active Sheet(s) as PDF
ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, _
   Filename:=saveLocation & saveName

End Sub

Answer 1

Excel stores worksheets as a collection, so you can easily access all the worksheets that have been created in the workbook. To allow user to select which ones he wants to export, you can create a UserForm with a ListBox that will read all available worksheets in the workbook and display them. Sample code that does that below (the UserForm has only one listbox created ListBox1 and nothing else).

Sub export_wsheets()
    Dim wsheet As Worksheet
    Dim wsheets_col As worksheets
    Dim uForm As New UserForm1

    Set wsheets_col = ThisWorkbook.worksheets

    For Each wsheet In wsheets_col
        uForm.ListBox1.AddItem wsheet.Name
    Next

    uForm.Show   
End Sub

From then on you can just save user's choice and loop through the workbooks again exporting the ones that were selected. You can access particular worksheet by using it's name or ID. It's not a complete solution but I hope it sheds some more light on your problem.