Worksheet Page reference

[Dazree]

Hello,

I am trying to print out a few worksheets as a Group. I would like to have sort of a content page, saying which worksheet is on which page or pages. Does anyone know the function to place a worksheet page reference on another worksheet?

Will be very grateful for that.

Thanks!

Dazree

[Conor]

Perhaps someone with more knowledge will come along and contradict me, but I really do not think this is possible using a function. I believe you will need to use VB in a macro.

I believe the worksheet tab (and name) is considered part of the frame and not part of the worksheet itself, and thus cannot be directly referenced by a formula or built-in function.

[Dazree]

Hi Conor,

Do you know how I can use VB thru macro to achieve this reference? Do you have some codes off your head?

I will be very grateful for that.

[Conor]

Try the following:

Please Login or Register  to view this content.

In my example the worksheets are named according to the military alphabet, which is why my sheet 1 is Alpha. You need to substitute the name of the tab where you want the Table of Contents. Also, I assumed the TOC would be the first tab. (This code will populate the cells on whichever sheet from which it is executed.) I also selected cell C3 arbitrarily as the start of the TOC. All these values in the example can be changed to fit your circumstances.

What you need to do is open the Visual Basic Editor (alt+F11), select insert from the menu and module from the choices. On the new white page that is presented to you, paste the code above, save and close the VBE window.

Go to the sheet on which you want to create the TOC, press alt+F8 to list the available macros. Run this one (should be the only one).

HTH

Conor

[Dazree]

Thank you, Connor.

From the Watch in VBA, I could see that your script works. However the list of Worksheets were being wrote on another Worksheet (the 4th tab) instead of the TOC which is the first tab.

Additionally, do you know how I can make a reference of the worksheet pages?

Thank you.

[Conor]

Add this line between the Dim line and the set line.

Please Login or Register  to view this content.

Make the appropriate sheet name change.

I don't know what you mean by "a reference of the worksheet pages".

[Dazree]

Thanks Connor for the tips.

About the reference of the worksheet pages. Ok, here is a short explanation.
I have printed the worksheets as a group and in the footer, there are pages. Which meant that all worksheets have pages allocated to them.

I was wondering if I could show alongside the names of the worksheet the pages as well. I hope you understand what I am trying to say.

[Conor]

That can probably be done, but it is outside the range of my knowledge. Since this has really turned into a macro issue, you should consider asking this question in the XL programming forum, rather than here in the function forum.

Conor