The "Print selected Sheets macro" prints unwanted last page

[DutchKevin]

Hello All,
I'm very impressed and happy with this code I found on the J-Walk website ray:
http://spreadsheetpage.com/index.php/tip/displaying_a_menu_of_worksheets_to_print/

It lets me select and print sheets from a large workbook. Very userfriendly.

I now use the suggested code at the bottom (from Aaron Blood) to have all pages print as one document, which is very convenient if printing it to a PDF-printer--> One file .
However, the code somehow always adds the last sheet to the print, even when that last slide is not selected in the checkbox list.

If the last sheet is empty, this does not occur....
Does anyone see a sollution for this?
My feeling is that it might have something to do with the part about :
' Add a temporary dialog sheet but i don't see the workaround.
I would not prefer to have an empty sheet at the back. This is because an other macro is used to copy-add new sheets, at the back of the range logically, from a template-sheet.

For complete code, please see above link.

Any help/advise is greatly appreciated.

Kevin

 

[daverunt]

Hi,

I tried the modification and got the same result as you. I have no idea why it prints the extra page so I added a valid checkbox count and it seems to print only those pages selected now.

'   Display the dialog box
    CurrentSheet.Activate
    Application.ScreenUpdating = True
    If SheetCount <> 0 Then
        If PrintDlg.Show Then
            num = 0
            For Each cb In PrintDlg.CheckBoxes
                If cb.Value = xlOn Then
                 num = num + 1
                 Worksheets(cb.Caption).Select Replace:=False
                End If
            Next cb
            
      If num = 0 Then
       MsgBox "No sheets selected for printing"
    Else
        ActiveWindow.SelectedSheets.PrintOut From:=1, To:=num, Copies:=1
    End If
    End If
    
    Else
        MsgBox "All worksheets are empty."
    End If

 

[DutchKevin]

Hi Daverunt
This works nicely. Thanks for taking the time to look into it and finding a sollution. I've implemented it right away and the problem is indeed solved now.

Many thanks again
Kevin

 

[DutchKevin]

Hi Daverunt, we might have been a bit to optimistic.
I tried it with one sheet consisting of more pages. So only 1 checkbox, but with your addition in code it prints only 1 page, where i would like to see more...
I feel we're close but not yet there. Any other ideas?

thanks
Kevin

 

[daverunt]

I think the Collate:=True is supposed to work but doesn't appear to on my PC.

ActiveWindow.SelectedSheets.PrintOut From:=1, To:=num, Copies:=1, Collate:=True

So I am out of ideas!