Number of pages displayed in cell

[Jimmypop]

Good day all

Some assistance required...

I need the number of pages for workbook (2 sheets)... hereafter is some info regarding the sheet/s

1. Sheet2 will always be 1 page (the last page)
2. Sheet1 starts of on 1 page and goes to a maximum of 8 pages.
3. Sheet1 in cell K2 should display the following Page 1 of 2 or Page 1 of 5 etc. depending on the number of pages on Sheet1 plus 1 page from Sheet2. (Number of pages on Sheet1 is determined by hiding and unhiding rows).
4. Sheet1 is in page break view.

So in short... after report is finished, K2 on Sheet1 should show the total number of pages of the workbook depending on the amount on Sheet1, e.g. Sheet1 has 4 pages so K2 should show Page 1 of 5 (4 pages from Sheet1 and 1 page from Sheet2) OR Sheet1 has 8 pages so K2 should show Page 1 of 9 (8 pages from Sheet1 and 1 page from Sheet2)...

Found this code below but it only shows the total of Sheet1 including hidden rows (Shows Page 1 of 8 all the time)

Sub pagenumber()
    'updateby Extendoffice 20160506
    Dim xVPC        As Integer
    Dim xHPC        As Integer
    Dim xVPB        As VPageBreak
    Dim xHPB        As HPageBreak
    Dim xNumPage    As Integer
    xHPC = 1
    xVPC = 1
    If ActiveSheet.PageSetup.Order = xlDownThenOver Then
        xHPC = ActiveSheet.HPageBreaks.Count + 1
    Else
        xVPC = ActiveSheet.VPageBreaks.Count + 1
    End If
    xNumPage = 1
    For Each xVPB In ActiveSheet.VPageBreaks
        If xVPB.Location.Column > ActiveCell.Column Then Exit For
        xNumPage = xNumPage + xHPC
    Next
    For Each xHPB In ActiveSheet.HPageBreaks
        If xHPB.Location.Row > ActiveCell.Row Then Exit For
        xNumPage = xNumPage + xVPC
    Next
    ActiveCell = "Page " & xNumPage & " of " & Application.ExecuteExcel4Macro("GET.DOCUMENT(50)")
End Sub

 

[JGordon11]

if you have no vertical page breaks you could use this function

Function Pages()
    Application.Volatile
    Pages = Application.Caller.Parent.HPageBreaks.Count + 1
End Function

Book5
	A	B	C	D	E	F	G	H	I	J	K	L
1	1
2	2										Page 1 of 3
3	3
4	4
5	5
6	6
7	7
8	8
9	9
10	10
11	11
12	12
13	13
14	14
15	15
16	16
17	17
18	18
19	19
20	20
21	21
22	22
23	23
24	24
25	25
26	26
27	27
28	28
29	29
30	30
31	31
32	32
33	33
34	34
35	35
36	36
37	37
38	38
39	39
40	40
41	41
42	42
43	43
44	44
45	45
46	46
47	47
48	48
49	49
50	50
51	51
52	52
53	53
54	54
55	55
56	56
57	57
58	58
59	59
60	60
61
Sheet1

Cell Formulas
Range	Formula
A1:A60	A1	=SEQUENCE(60)
K2	K2	="Page 1 of " & pages()+1
Dynamic array formulas.

 

[Jimmypop]

if you have no vertical page breaks you could use this function

Function Pages()
    Application.Volatile
    Pages = Application.Caller.Parent.HPageBreaks.Count + 1
End Function

Book5
	A	B	C	D	E	F	G	H	I	J	K	L
1	1
2	2										Page 1 of 3
3	3
4	4
5	5
6	6
7	7
8	8
9	9
10	10
11	11
12	12
13	13
14	14
15	15
16	16
17	17
18	18
19	19
20	20
21	21
22	22
23	23
24	24
25	25
26	26
27	27
28	28
29	29
30	30
31	31
32	32
33	33
34	34
35	35
36	36
37	37
38	38
39	39
40	40
41	41
42	42
43	43
44	44
45	45
46	46
47	47
48	48
49	49
50	50
51	51
52	52
53	53
54	54
55	55
56	56
57	57
58	58
59	59
60	60
61
Sheet1

Cell Formulas
Range	Formula
A1:A60	A1	=SEQUENCE(60)
K2	K2	="Page 1 of " & pages()+1
Dynamic array formulas.

Hi @JGordon11

Unfortunately I do have vertical page breaks as well... Code still gives pages as 8 but only 4 are visible and the rest are hidden...

 

[Jimmypop]

Has anyone else perhaps have any other ideas that could work?... Thanks