Dear Ashish, Thanks for your reply. But I have to admit that I am a novice so far as using VBA code is concerned. So it would be very kind of you , if you could be a detailed reply on how to use the solution provided by you.
Thanks & Regards... 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 -----Original Message----- From: excel-macros@googlegroups.com [mailto:excel-mac...@googlegroups.com] On Behalf Of Ashish Jain Sent: Tuesday, April 28, 2009 8:52 PM To: MS EXCEL AND VBA MACROS Subject: $$Excel-Macros$$ Re: PREPARE INDEX WITH PAGE NUMBERS 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 ------------------------------------------------------------------------------------- -~----------~----~----~----~------~----~------~--~---
