Microsoft Office Forums

Go Back   Microsoft Office Forums > >

Reply
 
Thread Tools Display Modes
  #1  
Old 09-02-2022, 11:44 PM
Marcia's Avatar
Marcia Marcia is offline Count number of pages Windows 11 Count number of pages Office 2021
Expert
Count number of pages
 
Join Date: May 2018
Location: Philippines
Posts: 526
Marcia has a spectacular aura aboutMarcia has a spectacular aura aboutMarcia has a spectacular aura about
Default Count number of pages

Hi. I found the below code that counts the number of printed pages. I would like the code to:


1. Show the number of pages in A1 of all the sheets in the workbook, not through msg box. If the selected print area of sheet1 is 10 pages, A1 of sheet1 should show 10. If sheet2 is 5 pages, A1 of sheet2 should also show 5.
2. The number of pages in A1 should be automatically shown.
Thank you.
Code:
Sub PageCount()
  MsgBox (ActiveSheet.HPageBreaks.Count + 1) * _
     (ActiveSheet.VPageBreaks.Count + 1)
End Sub
Reply With Quote
  #2  
Old 10-11-2022, 01:48 PM
BobBridges's Avatar
BobBridges BobBridges is offline Count number of pages Windows 7 64bit Count number of pages Office 2010 32bit
Expert
 
Join Date: May 2013
Location: USA
Posts: 700
BobBridges has a spectacular aura aboutBobBridges has a spectacular aura about
Default

So you already have a VBA program that calculates the number of pages, and displays it using MsgBox, right? If you want your program to put that number in a cell of a worksheet, the code looks something like this:
Code:
set ows = ActiveSheet
npg = (ows.HPageBreaks.Count + 1) * (ows.VPageBreaks.Count + 1)
set ocs = ows.Cells
ocs(1, 1).Value = npg 'row 1, col 1
There are other ways to code it; this is the way I probably would.
Reply With Quote
  #3  
Old 10-14-2022, 07:53 AM
Marcia's Avatar
Marcia Marcia is offline Count number of pages Windows 11 Count number of pages Office 2021
Expert
Count number of pages
 
Join Date: May 2018
Location: Philippines
Posts: 526
Marcia has a spectacular aura aboutMarcia has a spectacular aura aboutMarcia has a spectacular aura about
Default

Thank you Bob. I inserted your code to the first and the total page count appears in A1, but the macro says there are 3 pages when in fact the print area has 5 pages.
Reply With Quote
  #4  
Old 10-14-2022, 09:23 AM
BobBridges's Avatar
BobBridges BobBridges is offline Count number of pages Windows 7 64bit Count number of pages Office 2010 32bit
Expert
 
Join Date: May 2013
Location: USA
Posts: 700
BobBridges has a spectacular aura aboutBobBridges has a spectacular aura about
Default

I didn't check the formula you used; if it says 3 and it should be 5, seems to me there's something wrong with the calculation. All I was really trying to do is show you how to put that value in a cell rather than displaying it in a MsgBox.

But now I'm interested. I can see where counting the vertical and horizontal page breaks and multiplying them might give you more pages than the correct count; it isn't clear to me how it could be fewer. I don't fool with page breaks, myself, and have never looked at those properties. If I think of something in the next few days I'll probably come back and say more. But don't count on it; I think it's gotta be something wrong with the code you found and used.

I'm assuming, by the way, that when you used the MsgBox method, that too gave you the wrong count. My calculation is the same as yours, just phrased differently.
Reply With Quote
  #5  
Old 10-15-2022, 07:17 AM
Marcia's Avatar
Marcia Marcia is offline Count number of pages Windows 11 Count number of pages Office 2021
Expert
Count number of pages
 
Join Date: May 2018
Location: Philippines
Posts: 526
Marcia has a spectacular aura aboutMarcia has a spectacular aura aboutMarcia has a spectacular aura about
Default

You're right Bob, the msg box gave the same wrong answer, 3.
Reply With Quote
Reply

Thread Tools
Display Modes


Similar Threads
Thread Thread Starter Forum Replies Last Post
Count number of pages Number Pages - not total pages, but actual pages. Kiminator321 Word 8 04-29-2020 03:07 PM
Count number of days walidriachi Word VBA 5 07-23-2018 12:13 AM
Count number of pages count a number of cells based on the beginning of a order number Kubi Excel 2 08-06-2017 08:54 PM
Count number of pages Count Number of Pages in Document with Footnotes d4okeefe Word VBA 5 11-11-2013 11:02 AM
Count number of pages page count increases on its own -inserts white pages itsema Word 1 03-06-2013 12:48 PM

Other Forums: Access Forums

All times are GMT -7. The time now is 02:42 AM.


Powered by vBulletin® Version 3.8.11
Copyright ©2000 - 2024, vBulletin Solutions Inc.
Search Engine Optimisation provided by DragonByte SEO (Lite) - vBulletin Mods & Addons Copyright © 2024 DragonByte Technologies Ltd.
MSOfficeForums.com is not affiliated with Microsoft