Hi,
Try wsheet.Hpagebreaks.Count and Wsheet.VPageBreaks.Count.
and wsheet.HPageBreaks(<Page Count>).Location.Row
Regards
Ashish Jain
www.excelitems.com
Developer of OpenXL
---------------------------------------------------------------------------
On Apr 28, 9:24 am, "vivek jain" <vivek.j...@gmail.com> wrote:
> Dear All Groupmembers,
>
> I want to prepare an index sheet which will contain the name of all the
> sheets and their (continuous) page numbers (given in footer). The index
> should be able to update on any insertion of new sheet or on deletion of
> any. Further the page numbers should also be updated automatically. I had
> received a code from one of the group members which I am reproducing below
> which helps me in preparing index with sheet name. But I do not get the page
> numbers. It will be of great help if anyone can provide me the solution.
>
> Code for index:
>
> Private Sub Worksheet_Activate()
>
> Dim wSheet As Worksheet
>
> Dim M As Long
>
> M = 1
>
> With Me
>
> .Columns(1).ClearContents
>
> .Cells(1, 1) = "INDEX FOR FINANCIALS"
>
> .Cells(1, 1).Name = "Index"
>
> End With
>
> For Each wSheet In Worksheets
>
> If wSheet.Name <> Me.Name Then
>
> M = M + 1
>
> With wSheet
>
> .Range("H1").Name = "Start" & wSheet.Index
>
> .Hyperlinks.Add Anchor:=.Range("H1"), Address:="",
> SubAddress:="Index", TextToDisplay:="Back to Index"
>
> End With
>
> Me.Hyperlinks.Add Anchor:=Me.Cells(M, 1), Address:="",
> SubAddress:="Start" & wSheet.Index, TextToDisplay:=wSheet.Name
>
> End If
>
> Next wSheet
>
> End Sub
>
> Thanks & Regards...
>
> cid:image001....@01C85EB8.0483F790 Vivek Jain, FCA, DISA (ICAI)
>
> Vivek Shantilal Jain & Co.
>
> Chartered Accountants
>
> 3010 Ram Kumar Arcade,
>
> Chatribari Road, A T Road,
>
> Guwahati-781001
>
> (0361-2603895,9435019908
>
> image001.gif
> 2KViewDownload
>
> image002.jpg
> 1KViewDownload
--~--~---------~--~----~------------~-------~--~----~
-------------------------------------------------------------------------------------
Some important links for excel users:
1. Excel and VBA Tutorials(Video and Text), Free add-ins downloads at
http://www.excelitems.com
2. Excel tutorials at http://www.excel-macros.blogspot.com
3. Learn VBA Macros at http://www.vbamacros.blogspot.com
4. Excel Tips and Tricks at http://exceldailytip.blogspot.com
To post to this group, send email to excel-macros@googlegroups.com
If you find any spam message in the group, please send an email to:
Ayush Jain @ jainayus...@gmail.com or
Ashish Jain @ 26may.1...@gmail.com
-------------------------------------------------------------------------------------
-~----------~----~----~----~------~----~------~--~---
