Page number "1/3" in C7 that repeats top of each page in a sheet, Excel VBA

[Mirkaminer]

I have a workbook with several sheets. On every sheet i repet row 1 to 13 on each page. In cell C7 i want page number and total number of pages in the format of "1/3".

I have found this VBA code to do part of the problem

placed this part in a module

    Sub PageNumber(MyRange As String)
    Dim iVPC As Integer
    Dim iHPC As Integer
    Dim iVPB As VPageBreak
    Dim iHPB As HPageBreak
    Dim iNumPage As Integer
    iHPC = 1
    iVPC = 1
    If ActiveSheet.PageSetup.Order = xlDownThenOver Then
        iHPC = ActiveSheet.HPageBreaks.Count + 1
    Else
        iVPC = ActiveSheet.VPageBreaks.Count + 1
    End If
    iNumPage = 1
    For Each iVPB In ActiveSheet.VPageBreaks
        If iVPB.Location.Column > ActiveCell.Column Then Exit For
        iNumPage = iNumPage + iHPC
    Next
    For Each iHPB In ActiveSheet.HPageBreaks
        If iHPB.Location.Row > ActiveCell.Row Then Exit For
        iNumPage = iNumPage + iVPC
    Next
    MyRange = "'" & iNumPage & "/" & Application.ExecuteExcel4Macro("GET.DOCUMENT(50)")

    End Sub

placed this part in the sheet object window

    Private Sub Worksheet_Activate()
        Dim StrString As String
        Call PageNumber(StrString)
        Range("C7:C7").Value = StrString
    End Sub

The part i have problems with is that i need iNumPage (the current page number, not the total number of pages)to update for every page that is going in to print.
Is there a way maybe piggyback riding of the page number function from header and footer, and using it in Private Sub Workbook_BeforePrint or is there another solution. The page number is only for cell C7 because of the repet of row 1-13 on top of each page in print out.

 

[BSALV]

you should look to add that current page in the header, there is no other option i think.
And then the annoying thing to align your header with your line 7 ...

 

[Mirkaminer]

I

you should look to add that current page in the header, there is no other option i think.
And then the annoying thing to align your header with your line 7 ...

Isn't that weird that they have a function or formula for almost every thing but not that....??

 

[BSALV]

do you print or export to a pdf ?
otherwise, with VBA, you can print page by page and change C7 after every page.

 

[Mirkaminer]

Well i'm just making the template, so it upp to others to use it and print the sheet.

 

[BSALV]

it's akward but if you don't have another choice.

Sub print_page_per_page()
     With ActiveSheet
          .PageSetup.PrintArea = "A1:J1000"
          .PageSetup.PrintTitleRows = "$1:$7"

          For i = 1 To 20
               .Range("C7").Value = "printing now page number " & i
               .PrintOut i, i, 1, True                          'PrintOut with previeuw page per page
          Next
     End With
End Sub

 

[Mirkaminer]

I know, if i just could get past it printing once fore each page and that Workbook_BeforePrint repeat twice for each.

Private Sub Workbook_BeforePrint(Cancel As Boolean)
Dim sht As Worksheet
Dim LastRow As Long
Dim I As Long
Dim A As Long
Dim iHpBreaks As Integer, iVBreaks As Integer

Set sht = ActiveSheet

iHpBreaks = sht.HPageBreaks.Count + 1
iVBreaks = sht.VPageBreaks.Count + 1

A = iHpBreaks * iVBreaks

LastRow = sht.Cells(sht.Rows.Count, "A").End(xlUp).Row
     With sht
          .PageSetup.PrintArea = "A1:L" & LastRow
          .PageSetup.PrintTitleRows = "$1:$13"

          For I = 1 To A
               .Range("C7").Value = "'" & I & "/" & A
               .PrintOut I, I, 1, False                          'PrintOut with previeuw page per page
          Next
     End With
End Sub

This got some type of result but not very good. C7 2/2 on page 1, C7 2/2 on page 2, this happens because Workbook_BeforePrint repeats twice for each page.

 

[BSALV]

if you add "application.enableevents=false" at the beginning and the same=true at the end, you enable that event during your printing.
Caution, if the macro is stopped (due to an error or stopped by yourself) before re-enabling the events, you have to do it yourself, if you are using events somewhere

Private Sub Workbook_BeforePrint(Cancel As Boolean)
Dim sht As Worksheet
Dim LastRow As Long
Dim I As Long
Dim A As Long
Dim iHpBreaks As Integer, iVBreaks As Integer

application.enableevents=false 
Set sht = ActiveSheet
....
     End With
application.enableevents=true 
End Sub

 

[Mirkaminer]

The result is still in "C7" 2/2 on page 1, "C7" 2/2 on page 2.

 

[BSALV]

That beforeprint-event, eliminate that one for a moment.
What does this macro do ?

Sub Print_This_Sheet()
     Dim i, iTot_pages
     Application.EnableEvents = False                           ' no interference with workbook_BeforePrint !!!!

     With ActiveSheet
          .PageSetup.PrintArea = "A1:H190"
          .PageSetup.PrintTitleRows = "$1:$7"
          MsgBox "and other pagesetup-setting ....", vbInformation
          iTot_pages = Application.ExecuteExcel4Macro("GET.DOCUMENT(50)")     '----> normally the number of pages

          For i = 1 To iTot_pages                               'loop through those pages
               .Range("C7").Value = "printing now page number " & i & "/" & iTot_pages     'before printing, ajust C7
               .PrintOut i, i, 1, True                          'PrintOut with previeuw page per page'print one page
          Next
     End With

     Application.EnableEvents = True                            'enable events again
End Sub