Dynamic page number

Hidden Dan

Board Regular
Joined
Dec 7, 2016
Messages
63
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'.

VBA Code:
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.
 

Some videos you may like

Excel Facts

Best way to learn Power Query?
Read M is for (Data) Monkey book by Ken Puls and Miguel Escobar. It is the complete guide to Power Query.

yky

Well-known Member
Joined
Jun 7, 2011
Messages
1,816
Office Version
  1. 2010
Platform
  1. Windows
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.

VBA Code:
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
 
Last edited:

yky

Well-known Member
Joined
Jun 7, 2011
Messages
1,816
Office Version
  1. 2010
Platform
  1. Windows
Color code needs to be removed.

VBA Code:
       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
 

Watch MrExcel Video

Forum statistics

Threads
1,123,281
Messages
5,600,711
Members
414,401
Latest member
grenona2020

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top