Dynamic page number

[Hidden Dan]

Hello,

I create some Word / Excel documents which are combined into 1 PDF. No problem at all here.

However, I struggle with pagination of the Excel part. Depending on last page of Word document the Excel must start with a certain number. This can be any number like 30, 40 or 43. The Excel itself consists of 30-40 different sheets, each representing 1 single page. Also, the Excel needs header and footers which are now hard coded by a VBA script.

The script I use was found elsewhere and is called 'xlStdHeaderFooter'. It has a pagination option for the RightFooter. But this works fine if you start with Page 1. But I'm currently looking for an addition that seeks for the starting page number. This starting page number can be found on a sheet called 'Config', which basically contains all kind of generic information regarding the spreadsheet.

Say, starting page number is 40 and can be found in cell F8 on sheet 'Config'.

Please find below the script which is placed in 'ThisWorkbook'.

Sub xlStdFooter()
'
'****************************************************************************************
'       Title       xlStdFooter
'       Target Application:  MS Excel
'       Function:   builds standard Excel footer
'               a call to this subroutine is added to the Workbook_BeforePrint proc
'               in the "ThisWorkbook" code module
'
'****************************************************************************************
'
'
    ActiveSheet.PageSetup.LeftFooter = _
        "File:   " & ActiveWorkbook.Name & vbLf & _
        "Tab:    " & ActiveSheet.Name
    ActiveSheet.PageSetup.CenterFooter = _
        "Date Printed:   " & Format(Date, "dd-mmm-yyyy") & vbLf & _
        "Time Printed:   " & Format(Time, "hhmm") & " hrs"
    ActiveSheet.PageSetup.RightFooter = _
        "Page #:      " & "&P" & vbLf & _
        "Total Pages: " + "&N"

End Sub

I'm sorry for the explanation but I hope it makes my problem clear.


Thank you for any help.

 

[yky]

I tested the following code with two scenarios, A31=27 and A131=127, and got the desired page numbers, 27 and 127. Subsequent pages were also fine, one starting from 28, 29 and on, the other 128, 129 and on. You can adapt it to suit your needs. I have had the same question as yours for quite a long time, finally got it solved today.

Sub xlStdFooter()
'
'****************************************************************************************
'       Title       xlStdFooter
'       Target Application:  MS Excel
'       Function:   builds standard Excel footer
'               a call to this subroutine is added to the Workbook_BeforePrint proc
'               in the "ThisWorkbook" code module
'
'       Modified by yky on 2020/06/17
'****************************************************************************************
'
'
Dim nn As Integer
Dim pagenum As String

nn = ThisWorkbook.Sheets("Sheet1").Range("a31").Value  

pagenum = "&p" & "+" & nn - 1

    ActiveSheet.PageSetup.LeftFooter = _
        "File:   " & ActiveWorkbook.Name & vbLf & _
        "Tab:    " & ActiveSheet.Name
    ActiveSheet.PageSetup.CenterFooter = _
        "Date Printed:   " & Format(Date, "dd-mmm-yyyy") & vbLf & _
        "Time Printed:   " & Format(Time, "hhmm") & " hrs"
    ActiveSheet.PageSetup.RightFooter = _
        "Page #:      " & [COLOR=rgb(184, 49, 47)]pagenum[/COLOR] & vbLf & _
        "Total Pages: " + "&N"

End Sub

 

[yky]

Color code needs to be removed.

       Sub xlStdFooter()
'
'****************************************************************************************
' Title xlStdFooter
' Target Application: MS Excel
' Function: builds standard Excel footer
' a call to this subroutine is added to the Workbook_BeforePrint proc
' in the "ThisWorkbook" code module
'
' Modified by yky on 2020/06/17. The page number starts from the value stored in A31.
'****************************************************************************************
'
'
Dim nn As Integer
Dim pagenum As String

nn = ThisWorkbook.Sheets("Sheet1").Range("a31").Value

pagenum = "&p" & "+" & nn - 1

ActiveSheet.PageSetup.LeftFooter = _
"File: " & ActiveWorkbook.Name & vbLf & _
"Tab: " & ActiveSheet.Name
ActiveSheet.PageSetup.CenterFooter = _
"Date Printed: " & Format(Date, "dd-mmm-yyyy") & vbLf & _
"Time Printed: " & Format(Time, "hhmm") & " hrs"
ActiveSheet.PageSetup.RightFooter = _
"Page #: " & pagenum & vbLf & _
"Total Pages: " + "&N"

End Sub

 

[Hidden Dan]

OK, thanks for the addition.

Dan