Julie said:
I have a worksheet that is generated by Buisness Intellegience application. The report generates over 400 worksheets.
I need a way to create an index that shows a combintation of both the worktab and the first two rows of each worksheet.
Is that a possibility?
Thanks,
Jule
EggHeadCafe - Software Developer Portal of Choice
Bspline Curves in c#
http://www.eggheadcafe.com/tutorial...5d-8bb9-19ccac9133ab/bspline-curves-in-c.aspx
'I wrote the code below to create an INDEX page with Hyperlinks to each
page/tab. It only works on sheet/tab names. It not exactly what you
asked for but you could add/modify the code to get the first two rows
from each worksheet.
Sub BuildIndex()
'<<<<<< Create Index Page (if it doesn't exist)>>>>>
On Error GoTo ErrorHandler
Sheets("Index").Select
If ActiveSheet.Name = "Index" Then
Else
ErrorHandler:
Sheets.Add
ActiveSheet.Name = "Index"
Sheets("Index").Move Before:=Sheets(2)
End If
'<<<<< Clear Old HyperLinks From the Index page >>>>>
Columns("B:B").Select
Selection.Clear
Range("B4").Select
ActiveCell.FormulaR1C1 = "'a"
Range("B5").Select
ActiveCell.FormulaR1C1 = "'a"
Range("A1").Select
'<<<<< Setup Hyperlinks >>>>>>>>>
Dim CountSheets
Dim Lastsheet
CountSheets = Sheets.Count
Lastsheet = Sheets(CountSheets).Name
CurrentSheet = ActiveSheet.Name
Do
Do While CurrentSheet <> Lastsheet
Dim SheetName
Sheets("Index").Select
Range("B4").End(xlDown).Offset(1, 0).Select
ActiveSheet.Hyperlinks.Add Anchor:=Selection, Address:="", SubAddress:= _
("'" & (CurrentSheet) & "'" & "!A1"), TextToDisplay:=(CurrentSheet)
Sheets(CurrentSheet).Select
ActiveSheet.Next.Select
CurrentSheet = ActiveSheet.Name
Loop
Loop Until CurrentSheet = Lastsheet
Sheets("Index").Select
Range("B4").End(xlDown).Offset(1, 0).Select
ActiveSheet.Hyperlinks.Add Anchor:=Selection, Address:="", SubAddress:= _
("'" & (CurrentSheet) & "'" & "!A1"), TextToDisplay:=(CurrentSheet)
Sheets("Index").Select
Rows("4:5").Select
Selection.Delete Shift:=xlUp
Range("A1").Select
End Sub
