Macro button to print selected sheets and re-number them in the footer before printing

[Raceman]

I like the code that is shown in the below thread that allows me to create a separate sheet and put a "0" or "1" to select which sheets I need to print. But I also need to have these sheets print with sheet no.s filled out in the footer. The first sheet selected is Sheet 1 of X (X=the total no. of sheets selected). Any help would be appreciated.



http://www.mrexcel.com/forum/excel-questions/294329-macro-printing-sheets-selected-checkbox.html

 

[dermie_72]

you could copy and paste the selection into a new sheet, then print that showing the footer details. Then you wouldn't need to count the sheets you have selected.
Select the data.
Record a macro
Copy
Create a new sheet
Paste
Set up your footers the way you want.
Click print
Close the file without saving
Stop recording

Re-run the macro as required, once you have selected the data.

Any problems with this, let us know.

 

[Raceman]

If I understand your solution correctly it wouldn't work well in my application. I'll have people that aren't very familiar with Excel using this file a lot. I wanted to put a macro button on the sheet where I've made the "1" and "0" selections (print or not print, respectively). I'll call this sheet "Print Table" so the user can select the sheets he/she wants to print. After these selections are made, the user can press the macro button and the code will be executed to do the following:
1. print only the sheets that are designated with "1" in the Print Table Sheet.
2. make changes to the footers of those sheets that were selected in the Print Table. The first sheet selected in the Print Table (the "Cover Sheet") footer will read Sheet 1 of X, where X = total number of sheets selected. And the following sheets will have Sheet 2, Sheet 3, Sheet 4, etc

This is the code (below) that works for me for #1 above, from http://www.mrexcel.com/forum/excel-questions/294329-macro-printing-sheets-selected-checkbox.html

If I can add code to do #2 above then I will reach the objective.

Option Explicit

Sub DOPrintSheets()
    Dim prnSheets As Range
    Dim wkshtTemp As Worksheet
    
    Set prnSheets = Range("prnSheets").Offset(1, 0)
    
    While prnSheets <> ""
        If prnSheets.Offset(, 1) = 1 Then
            On Error Resume Next
            Set wkshtTemp = Worksheets(prnSheets.Text)
            On Error GoTo 0
            
            If wkshtTemp Is Nothing Then
                MsgBox "The following Sheet was indicated to print, but not found in the workbook:  " & prnSheets & "." & vbCr & "Program is halting"
                Exit Sub
            Else
            End If
            wkshtTemp.PrintOut
            
        End If
        
        Set wkshtTemp = Nothing
        Set prnSheets = prnSheets.Offset(1, 0)
    Wend
    
End Sub