Using Excel, I'd like to show the page number on the first sheet, where the start other sheets can be found.

[Tanquen]

I have a few sheets that are printed together to a single PDF file.
I'd like to get the page number where each sheet will start in the PDF and include it on the first sheet that is part of the larger PDF.



I found this a few other things that do not work.

How to determine how many pages will be printed in Excel - Microsoft Support

support.microsoft.com

 

[Tanquen]

If you want to print or save to PDF, you need to let the computer know what to print.
You could have a sheet with 250 rows of data but if you only want to print the first 75 rows, you need to let the computer know.
That would be the print range or range to be printed.

Sorry, still lost. Is that not just the Print Area? If there are rows with data outside the Print Area they are not printed. If there are 10 rows with data inside the Print Area those 10 rows are printed.

 

[jolivanes]

Yes, you're right (Post #11)

Re: "where each sheet will start in the PDF"
Don't know what that means.
All I know is you can have "Page 1 of 6", "Page 2 of 6", "Page 3 of 6" etc etc.
Or if you want to you can through horizontal page breaks have something like "From Row to Row 50", "From Row 51 to Row85", From Row 86 to Row 125" etc etc

 

[Tanquen]

Re: "where each sheet will start in the PDF"
Don't know what that means.

I just need the number of pages each sheet will print so I can place that info on the cover sheet.

Using this.

ActiveSheet.PageSetup.Pages.Count

This code works but it seems to only update after a print. I end up with three cells on the 'report_ws' sheet and a formula on the cover sheet 'cover_ws' uses them to show where the start of each sheet can be found in the PDF.

    ' Store the PageSetup.Pages.Count in cells T2, U2, and V2
    report_ws.Range("T2").Value = cover_ws.PageSetup.pages.Count
    report_ws.Range("U2").Value = report_ws.PageSetup.pages.Count
    report_ws.Range("V2").Value = Devation_ws.PageSetup.pages.Count

I then can display this on the cover sheet that is also in the print to PDF script.

This all works as I needed until some of the rows on the 'report_ws' sheet are hidden or unhidden. Using the same print to PDF script it has the wrong number of pages until you let the print to PDF script run.

 

[jolivanes]

Set your print range and run this.

Sub How_Many_Pages()
    MsgBox ExecuteExcel4Macro("GET.DOCUMENT(50)") & _
        " pages will be printed."
End Sub

 

[jolivanes]

Check the spelling and capitalization of "Devation_ws"

Sub Get_Pages()
Dim shArr, i As Long, j As Long, a As String
a = ActiveSheet.Name
j = 20
shArr = Array("cover_ws", "report_ws", "Devation_ws")    '<----- Check spelling! Deviation or Devation
Application.ScreenUpdating = False
    For i = Lbound(shArr) To Ubound(shArr)
        Sheets(shArr(i)).Select
            report_ws.Cells(2, j).Value = Application.ExecuteExcel4Macro("GET.DOCUMENT(50)")
        j = j + 1
    Next i
Sheets(a).Select
Application.ScreenUpdating = True
End Sub

 

[Tanquen]

Set your print range and run this.

Sub How_Many_Pages()
    MsgBox ExecuteExcel4Macro("GET.DOCUMENT(50)") & _
        " pages will be printed."
End Sub

Thanks for trying to help me with this. It seems like an easy thing to do but so far nothing is consistent.

Sub How_Many_Pages()
    MsgBox ExecuteExcel4Macro("GET.DOCUMENT(50)") & _
        " pages will be printed."
End Sub

The above code deletes my buttons from the sheet after running. No idea why but I have to reopen the workbook to get them back.

It gives me a number but when the workbook is first opened it gives a 1.
Again after printing a PDF and running the code again then it gives a 29, witch is correct. So again it works but it's always a print/update behind.

 

[jolivanes]

I don't know what causes that in your workbook because it works good here. Could be a 365 problem?

 

[Tanquen]

I don't know what causes that in your workbook because it works good here. Could be a 365 problem?

Could be but even without the buttons being removed the updates are still behind the print action.

This the Excel version: Microsoft® Excel® for Microsoft 365 MSO (Version 2409 Build 16.0.18025.20030) 64-bit

I may try a new workbook and see if it gets the number of pages right but it seems like the different ways to see how many pages will print require you to print first.

 

[jolivanes]

Re: "require you to print first." This would defeat the purpose.
Re: "when the workbook is first opened it gives a 1." How is that initialized? With a button after opening?

I made 3 sheets. Sheet1 data from A1 To I759, Sheet2 from A1 to I487 and Sheet3 from A1 to I250
With the macro from Post #15, the result is 17 pages for Sheet1, 11 pages for Sheet2 and 6 pages for Sheet3.
In PrintPreview it shows 47 rows per page. You can do the math yourself. It works on this trial book. I have not printed anything.
Can't see why buttons should disappear. Is some other piece of code initialized?

We also don't know which other, if you do have, macros you have.

I'll see if I can get another piece of code that uses pagebreaks.

 

[Tanquen]

With the macro from Post #15

When I try to run it there is a 'Subscript out of range' error on this line: Sheets(shArr(i)).Select

    Dim FilePath As String
    Dim DefaultPath As String
    Dim FileName As String
    Dim CurrentDateTime As String
    Dim desiredOrder As Variant
    Dim i As Long
    Dim cover_ws As Worksheet
    Dim report_ws As Worksheet
    Dim Deviation_ws As Worksheet
    
    ' Set references to the source and destination sheets
    Set cover_ws = ThisWorkbook.Sheets("Cover Sheet")
    Set report_ws = ThisWorkbook.Sheets("BMS vs Azure DB")
    Set Deviation_ws = ThisWorkbook.Sheets("Deviation Exceeded")

    Call CopyRowsDeviationExceeded
    Call CopyRowsMissingPoints
    
'    ' Store the PageSetup.Pages.Count in cells T2, U2, and V2
'    cover_ws.Range("O2").Value = cover_ws.PageSetup.pages.Count
'    cover_ws.Range("O3").Value = report_ws.PageSetup.pages.Count
'    cover_ws.Range("O4").Value = Deviation_ws.PageSetup.pages.Count
    
    Dim shArr, j As Long, a As String
    a = ActiveSheet.Name
    j = 20
    shArr = Array("cover_ws", "report_ws", "Deviation_ws")
    Application.ScreenUpdating = False
        For i = LBound(shArr) To UBound(shArr)
            Sheets(shArr(i)).Select
                report_ws.Cells(2, j).Value = Application.ExecuteExcel4Macro("GET.DOCUMENT(50)")
            j = j + 1
        Next i
    Sheets(a).Select
    Application.ScreenUpdating = True