If you'd like to use a listbox, I'd recommend setting up a userform with a listbox and a command button. The listbox will need to have multiselect enabled, which you can do from code.
Again, this will print out not only the selected worksheets, but also the selection from within each worksheet. You'll need to make sure the correct cells are highlighted within each worksheet before running code for your listbox.
It would not be neccessary to include page numbers in your code, because once that is set up (see answer above) it will stay that way until changed again. If you'd like it to stay that way forever, save the workbook after setting up custom footer.
Here's an example code to get you started. It assumes the userform is called UserForm, the listbox is ListBox1, and the command button is cmd_print. Any lines starting with ' are comments for the code after it.
Private Sub UserForm_Initialize()
'make sure listbox1 allows multiple selections
ListBox1.MultiSelect = fmMultiSelectExtended
'display all worksheets in listbox1
Dim sheet_choose As Long
For sheet_choose = 1 To Worksheets.Count
Private Sub cmd_print_Click()
'use no_selected_worksheets to tell
'if anything has been selected in
'listbox1. default = true b/c assumes
'nothing has been selected until
'finding out otherwise.
Dim no_selected_worksheets As Boolean
no_selected_worksheets = True
'go through listbox, check what has been
Dim list_choose As Long
For list_choose = 0 To ListBox1.ListCount - 1
'if worksheet has been selected in
'listbox, make worksheet selected
If ListBox1.Selected(list_choose) Then
If no_selected_worksheets Then
'if this is the first worksheet
'to be selected, make sure no
'other worksheets are also
Worksheets(list_choose + 1).Select
'change to false to indicate a
'worksheet has been selected.
no_selected_worksheets = False
'if this is *not* the first worksheet
'to be selected in the listbox, then
'make sure this worksheet is selected
'in addition to the other worksheets.
Worksheets(list_choose + 1).Select (False)
'if selection(s) made, printout. this will print only
If Not no_selected_worksheets Then
'as alternative to ActiveWorkbook.PrintOut, use
'display a message if nothing was selected
MsgBox "No sheets were selected for printing as .pdf", vbInformation, "PDF printer"
More help on any of these functions can be accessed through VBA help.