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

 

[jolivanes]

So you want the number of the first row of each page where?
Your request "on the first sheet that is part of the larger PDF" does not mean anything.
"In Column a starting at the first row" means something as does "in Range("A45") and subsequent each cell to the right."

 

[Tanquen]

So you want the number of the first row of each page where?
Your request "on the first sheet that is part of the larger PDF" does not mean anything.
"In Column a starting at the first row" means something as does "in Range("A45") and subsequent each cell to the right."

No, "the page number where each sheet will start in the PDF".

If you only read that part but there is more than that there. ???
"I'd like to get the page number" ← This would be a page number. A page number like page 13 of 38 pages.
"where each sheet will start in the PDF" ← This would be where each sheet starts. The PDF has 38 pages, where is the first page that each sheet starts on? The first sheet in the PDF starts on page 1 and then the next sheet starts on what page?
"and include it on the first sheet that is part of the larger PDF." ← This is the first sheet, that would be the first one in the larger PDF.

Yes, those all mean something but are not relevant.

Is there a way to find the number of pages that a sheet will have? Like in the linked example from MS but that will work or can be made to work.

 

[jolivanes]

MsgBox ActiveSheet.PageSetup.Pages.Count

This should show the pages in the print area

 

[Tanquen]

MsgBox ActiveSheet.PageSetup.Pages.Count

This should show the pages in the print area

This seems to work, even when rows are filtered out but sometimes it's not updating. I can run the report and it will so the same number of pages.

If I change the value manually in the cell to something else then it updates to the new value. ???

 

[jolivanes]

Do you save the file after making changes? If not, you should.
Put the Page # etc in the footer.

 

[Tanquen]

Now I see that it is an update behind but how is that working?

I run the script and the number of pages is 59.

I run it again after hiding some rows but don't print and it is still 59. I change nothing and run it again after a print and then it updates to 30.

Like 'PageSetup' only updated after you print?

Saving each time did not help.

 

[jolivanes]

What is the print range set to?

 

[Tanquen]

What is the print range set to?

Sorry, not sure what that is or where it is?
Is it the Print Area?

 

[jolivanes]

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.

Formatting and VBA codes for headers and footers

Office VBA reference topic

learn.microsoft.com

Insert page numbers on worksheets - Microsoft Support

Add page numbers or other text to a header or footer. Learn what a header and footer is, and how to find them in Excel.

support.microsoft.com

Set or clear a print area on a worksheet - Microsoft Support

If you frequently print a specific section of your worksheet, you can set a print area for it. That way, when you print your worksheet, only that section will print. You can enlarge the print area by adding adjacent cells. You can add cells to expand the print area as needed, and you can clear...

support.microsoft.com