Excel 2013, page-numbers on different printing-options

Hello all,

I have a workboot with several sheets and each sheet can be multiple papers when printed out.
I added a footer with &[Page]/&[Pages] to show e.g. 1/3.
However this behaves differently depending on how I print.
If I only print the active sheet then I get 1/3, 2/3, 3/3, however if I print the whole book then I get e.g. 1/17, 2/17, etc.

How can I get Excel to just calculate the page-numbers based on individual sheets instead of dependencies of the print-process?

Cheers,

Moadll

Hello Jeff,

that's unfortunate. The macro itself would be a theoretical way, however as the file will be handled any "random" person that's just asking for trouble. "Why should I click on something else if I have the print-button there...?"

I guess I'll have to got the old-fashioned "brute force and ignorance approach" and create text-boxes with the appropriate content when I create the file.

Cheers,

Martin

There is a VBA event called BeforePrint that can trap the user's Print command. If the user presses the Print button, the macro can replace that with something else. This does that and prints each sheet individually. The user never knows the difference.

Private Sub Workbook_BeforePrint(Cancel As Boolean)

   Dim WS As Worksheet
   
   Cancel = True
   
   For Each WS In Worksheets
      WS.PrintOut
   Next WS
   
End Sub

Hello 6StringJazzer,

thanks for the pointer, that helped. I had to put in a bit of stuff additionally to catch the difference between the original print and the print of the individual work-sheets as otherwise every print would have been cancelled.
It looks like that as total:

Dim WS_Number As Long
Dim CurrentSheet As Long
Dim BolIndividualPrint As Boolean
Dim BolPrintDone As Boolean
Private Sub SheetPrint()
    Dim WS As Worksheet
    For Each WS In Worksheets
        If CurrentSheet = WS_Number - 1 Then
            BolIndividualPrint = False
            BolPrintDone = True
        End If
        WS.PrintOut
        CurrentSheet = CurrentSheet + 1
    Next WS
End Sub



Private Sub Workbook_BeforePrint(Cancel As Boolean)
   If Not BolIndividualPrint Then
      Cancel = True
      If Not BolPrintDone Then
        WS_Number = Worksheets.Count
        CurrentSheet = 0
        BolIndividualPrint = True
        Call SheetPrint
      Else
        BolPrintDone = False
      End If
   End If
End Sub

As additional icing on the cake that code needs to be placed into a generated file which is only used for the printout. For that I went for the programmatic editing of that generated file:

Sub AddSht_AddCode()
    Dim wb As Workbook
    Dim xPro As VBIDE.VBProject
    Dim xCom As VBIDE.VBComponent
    Dim xMod As VBIDE.CodeModule
    Dim xLine As Long

    With wb
        Set xPro = .VBProject        
        Set xCom = xPro.VBComponents("ThisWorkbook")
        Set xMod = xCom.CodeModule

        With xMod
            xLine = .CountOfLines + 1
            .InsertLines xLine, "Dim WS_Number As Long"
            xLine = xLine + 1
            <...>
            .InsertLines xLine, "Private Sub SheetPrint()"
            xLine = xLine + 1
            .InsertLines xLine, "    Dim WS As Worksheet"
            xLine = xLine + 1
            <...>
            xLine = .CreateEventProc("BeforePrint", "Workbook")
            xLine = xLine + 1
            .InsertLines xLine, "   If Not BolIndividualPrint Then"
            xLine = xLine + 1
            <...>
        End With
    End With
    ActiveWorkbook.Save
end sub

So thanks a lot for the pointer.

Cheers,

Moadll