page number on Multiple Tabs

system · January 2, 2003, 9:43pm

Is there a way to get the page number to be the number of the entire report and not just the page number of that tab. I.E. If you have 9 tabs and each one is a page long, can you get the page numbers to show up as 1,2,3,4,5,6,7,8,9, instead of 1,1,1,1,1…?

Scott Bowers (BOB member since 2002-09-30)

system · January 2, 2003, 9:47pm

The only way I ever “got around” this was when I knew my first three tab were one page summaries… so I could just add the number of pages to the detail tab.

Come to think of it, though, there might be a VBA solution…

Good luck!

JennFisher (BOB member since 2002-06-25)

system · January 2, 2003, 10:13pm

A VBA solution can be made but it can be cumbersome and I am not sure how many would like it. But here goes nothin…

Create a variable on every Tab, Call it PgTab1, 2 [=Page()] and use it instead of Page() function in all your reports.
Add the following macro to the report and run it. It will populate the report page numbers. It is recommended that you refresh the report within the macro to get the fresh number of pages.

Sub NumberRep()

    ActiveDocument.Refresh
    PageNum = 0
   
    For i = 2 To ActiveDocument.Reports.Count
        PageNum = PageNum + ActiveDocument.Reports(i - 1).NumberOfPages
        DocumentVariables.Item("PgTab" &amp; i).Formula = "= Page() + " &amp; PageNum
        ActiveReport.ForceCompute
    Next i

End Sub

Kindly post if you see any problems or have any ideas for improvements…

avaksi (BOB member since 2002-08-22)

system · January 2, 2003, 10:23pm

Well, this isn’t very dynamic, but what about this:

On the first tab, use Page()
On the second tab, use Page()+1
On the third tab, use Page()+2
etc.

Or, if you want to to have Tab#.Page#, for example, on tab 2, you could have 2.1, you could use something like this:

=FormatNumber(Page()+1 ,"#")&amp;"."&amp;FormatNumber(Page() ,"#")

MichaelWelter (BOB member since 2002-08-08)

system · March 25, 2003, 9:47pm

Ok, I used avaksi’s solution and modified it so it is dynamic. First I created a variable for each tab that is named exactly the same as the tab name. Then I used this code:


Sub PageNumberTabs()

    PageNum = 0
    
    For i = 1 To ActiveDocument.Reports.Count
                
        If i <> 1 Then
            DocumentVariables.Item(ActiveDocument.Reports.Item(i).Name).Formula = "= " &amp; Chr(34) &amp; "Page " &amp; Chr(34) &amp; " &amp; FormatNumber (Page() + " &amp; PageNum &amp; ", ""#"")"
        Else
            DocumentVariables.Item(ActiveDocument.Reports.Item(i).Name).Formula = "= " &amp; Chr(34) &amp; "Page " &amp; Chr(34) &amp; " &amp; FormatNumber (Page() , ""#"")"
        End If
        
        ActiveReport.ForceCompute
        
        PageNum = PageNum + ActiveDocument.Reports(i).NumberOfPages
        
    Next i
End Sub

I will have this called from the afterrefresh() event. I tested it and it numbers all the tabs correctly. Even if you move the tabs around, there is no code changes that have to occur. I will probably make this an addin for some other reports.

Scott Bowers (BOB member since 2002-09-30)

system · April 2, 2003, 8:19pm

I edited my post to change the code, it now is tested and working, original code didn’t number quite right.

Scott Bowers (BOB member since 2002-09-30)

system · June 4, 2004, 9:01am

Hi,

I used the code in my macro, but it is not working. I have two reports in a document. I need to sequentially display the page numbers.

Is there anything else to do?

Sudhakar

ssudhakar (BOB member since 2004-03-01)

system · August 7, 2006, 6:13pm

I am trying to use this macro. have few doubts.

when i create a varible named same as the tab, what should be the formula i should be using?

I used

=Page()

After running the macro, i still get page numbered 1 across all the tabs.

i think since i am using page(), i am getting 1 across all tabs. What function/formula i should be using to get the running count of the tabs.

KhoushikTTT (BOB member since 2005-02-24)

system · August 7, 2006, 6:55pm

NEO,
Are you sure, you are calling the macro in after_refresh event.
The code will assign the value to the variables after refresh, for those variables which you said created same as tab names.

I would suggest assign zero(0) OR “”(double quotes) to the variables depending on the return type and try.

BO_Chief (BOB member since 2004-06-06)

system · August 7, 2006, 7:09pm

No, Could you guide me how to do this.
I dont know how to define the after_refresh event

Now i have just the above code in the editor.

KhoushikTTT (BOB member since 2005-02-24)

system · August 7, 2006, 7:59pm

@NEO:
Go to Tools–>Macro --> Click Visual Basic Editor --> In the project pane (on left) -->
double click ‘ThisDocument’.

Now in the right editor will appear, on the top you will see General/Document, Select
Document, just on the right select the event AfterRefresh.

A two line VBA code is generated for you automatically.
Paste the code which is in this thread BELOW the ‘End Sub’.

And Now you have to call this procedure.

Type PageNumberTabs in between the code which is generated for you automatically.

Hope that helps.

BO_Chief (BOB member since 2004-06-06)

system · August 7, 2006, 8:17pm

Searching for a word but, didnt get one. So, let my silence convey my feeling.

I will work on a sample data and post the report later this week.

KhoushikTTT (BOB member since 2005-02-24)

system · September 6, 2006, 6:00pm

Here is a sample report.

I created four tabs, as 1998, 1999, 2000 and All. Created four varibles named same as the Tabs

1998=Page()
1999=Page()
2000=Page()
   All=Page()

VB editor (Alt F11), I wrote this code

Private Sub Document_AfterRefresh()
Call PageNumberTabs
End Sub

Sub PageNumberTabs()

    PageNum = 0
    
    For i = 1 To ActiveDocument.Reports.Count
                
        If i <> 1 Then
            DocumentVariables.Item(ActiveDocument.Reports.Item(i).Name).Formula = "= " &amp; Chr(34) &amp; "Page " &amp; Chr(34) &amp; " &amp; FormatNumber (Page() + " &amp; PageNum &amp; ", ""#"")"
        Else
            DocumentVariables.Item(ActiveDocument.Reports.Item(i).Name).Formula = "= " &amp; Chr(34) &amp; "Page " &amp; Chr(34) &amp; " &amp; FormatNumber (Page() , ""#"")"
        End If
        
        ActiveReport.ForceCompute
        
        PageNum = PageNum + ActiveDocument.Reports(i).NumberOfPages
        
    Next i
End Sub

Now in the footer, I added these variables to the corresponding tabs.
Page Number Across Tabs.zip (20.0 KB)

KhoushikTTT (BOB member since 2005-02-24)

system · April 11, 2007, 9:25pm

This solution worked wonderfully. Thanks!
Can anyone suggest how to get the Page 1 of Page N in the same situation. Right now, its generating Page 1 to N , but i dont know how to get the total number of pages count and show it in all the reports.

san_gee (BOB member since 2006-11-01)

system · April 11, 2007, 9:40pm

=<Current tab object>&amp;" of "&amp;Right(<Last tab object)> ,2)

For Example

=<1998>&amp;" of "&amp;Right(<All)> ,2)

KhoushikTTT (BOB member since 2005-02-24)

system · April 11, 2007, 9:43pm

I did try that. But my last report has multiple sheets. eg. my last report has pages 18, 19 and 20
And this one gives me 18 as the total pages and not 20

san_gee (BOB member since 2006-11-01)

system · July 3, 2007, 8:01pm

You probably don’t need this answer anymore, but for anyone else with the same question, you can add a line of code to the macro and it will insert the total number of pages into another variable. I called mine LastPage. Then you will just need to add & "of " & to your formulas and all will be fine.

Sub PageNumberTabs()

…(same code as posted earlier)…

    PageNum = PageNum + ActiveDocument.Reports(i).NumberOfPages
   
Next i
'The following line is the one I added
DocumentVariables.Item("LastPage").Formula = PageNum

End Sub
[/b]

nrf-bob (BOB member since 2007-07-03)

system · November 8, 2007, 5:37pm

My VB is very very rusty…

I’ve added your amendment to get the “page x of y” but it doesn’t seem to give me the right numbers.
I have a 3 tab, 13 page report
Tab 1 -Notes - 1 page
Tab 2 -Directorate - 2 pages
Tab 3 -Buyer - 9 pages

The pages sequentially number perfectly (thanks) although the final part giving the total number of pages is having problems.
i.e the second tab numbers, Page 2 of 4, Page 5 of 5… the thrid tab also has problems.

I have the VB as you have above, I created variables just like you did in your sample report and I have done the page numbers as you suggest.

Hope someone can help - many thanks in advance

malc001 (BOB member since 2005-09-26)

system · November 8, 2007, 6:13pm

Well, maybe your math is a little rusty as well.

1+2+9=12, not 13.

MichaelWelter (BOB member since 2002-08-08)

system · November 9, 2007, 8:41am

Thanks for your constructive reply.

If you can’t help out then why do you bother posting? Or are you just trying to improve your post count?

If any one can offer some serious help I would be greatly appreciate it.

malc001 (BOB member since 2005-09-26)