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.jpg@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
--~--~---------~--~----~------------~-------~--~----~
-------------------------------------------------------------------------------------
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
-------------------------------------------------------------------------------------
-~----------~----~----~----~------~----~------~--~---
<<inline: image001.gif>>
<<inline: image002.jpg>>
