Take the 2-minute tour ×
Super User is a question and answer site for computer enthusiasts and power users. It's 100% free, no registration required.

I have a Workbook that contains multiple tabs that are used to list data that is printed for a report. When it is time to print the report, a user opens a switchboard and selects the desired sections (Worksheets) of the Workbook that they would like to print.

I did not make this spreadsheet and, I'll be honest, I have little experience working with Excel at this level.

Currently, when the worksheets are printed, each one is printed as it's own document to a PDF. Once all worksheets are printed to PDF, the user opens Adobe Acrobat and merges the PDFs together.

My users would like for Excel to print only the selected worksheets to a single, PDF and to include sequential page numbers across the entire document. Can this be done (via Macros or VBA?)

share|improve this question

2 Answers 2

up vote 1 down vote accepted

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
    ListBox1.AddItem (Worksheets.Item(sheet_choose).Name)
  Next
End Sub

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
  'selected.
  Dim list_choose As Long
  For list_choose = 0 To ListBox1.ListCount - 1
    'if worksheet has been selected in
    'listbox, make worksheet selected
    'in workbook.
    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
        'selected.
        Worksheets(list_choose + 1).Select
        'change to false to indicate a
        'worksheet has been selected.
        no_selected_worksheets = False
      Else
        '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)
      End If
    End If
  Next

  'if selection(s) made, printout. this will print only
  'the selections.
  If Not no_selected_worksheets Then
    'as alternative to ActiveWorkbook.PrintOut, use
    'ActiveWorkbook.PrintPreview
    ActiveWorkbook.PrintOut
  Else
    'display a message if nothing was selected
    MsgBox "No sheets were selected for printing as .pdf", vbInformation, "PDF printer"
  End If
End Sub

More help on any of these functions can be accessed through VBA help.

share|improve this answer

To print only selected worksheets, you actually have to select twice.
First, select ranges you want printed from each worksheet. If only one cell is selected, only once cell will print for that worksheet.
Second, select worksheets you want to print by clicking and ctrl-clicking the tabs for worksheets.

To have page numbers printed across the document, choose "Page Layout" from the ribbon, then click on the arrow at the bottom right of the "Page Setup" section. Click on the "Header/Footer" tab, choose "Custom Footer...", and you can place the page number where desired by choosing the page number icon for the appropriate section.

I'd recommend doing a print preview first, to see if it matches what you want, before printing to .pdf.

share|improve this answer
    
I know this is SuperUser (and code questions tend to belong on StackOverflow) but do you know how to do this in VBA? My users want a list box that they can use multi-select from the sections of the Excel document. This is actually a good requirement because there are a LOT of worksheets and mis-clicking, when selecting worksheets, is a very easy thing to do. –  RLH Aug 30 '13 at 11:22

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.