Hi,

I have created an Index Sheet using the following code;

Private Sub Worksheet_Activate()
Dim wSheet As Worksheet
Dim l As Long
l = 1

With Me
.Columns(1).ClearContents
.Cells(1, 1) = "INDEX"
.Cells(1, 1).Name = "Index"
End With

For Each wSheet In Worksheets
If wSheet.Name <> Me.Name Then
l = l + 1
With wSheet
.Range("A1").Name = "Start" & wSheet.Index
.Hyperlinks.Add Anchor:=.Range("A1"), Address:="", _
SubAddress:="Index", TextToDisplay:="Back to Index"
End With

Me.Hyperlinks.Add Anchor:=Me.Cells(l, 1), Address:="", _
SubAddress:="Start" & wSheet.Index, TextToDisplay:=wSheet.Name
End If
Next wSheet
End Sub

The only problem i am having is i want the sheet to obtain various cell
values from each sheet and then place them in the relevant columns.

Does anyone have any ideas?

Thank you in advance.

After the Me.Hyperlinks line, use code like

Me.Cells(l, 2).Value = wSheet.Range("B2").Value
Me.Cells(l, 3).Value = wSheet.Range("C3").Value
Me.Cells(l, 4).Value = wSheet.Range("H9).Value
Me.Cells(l, 5).Value = wSheet.Range("M2").Value


or create links:

Me.Cells(l, 2).Formula = "='" & wSheet.Name & "'!B2"
Me.Cells(l, 3).Formula = "='" & wSheet.Name & "'!C3"
etc...

HTH,
Bernie
MS Excel MVP


"David Coyle" <(E-Mail Removed)> wrote in message
news:528B09EB-8DB1-4649-8252-(E-Mail Removed)...
> Hi,
>
> I have created an Index Sheet using the following code;
>
> Private Sub Worksheet_Activate()
> Dim wSheet As Worksheet
> Dim l As Long
> l = 1
>
> With Me
> .Columns(1).ClearContents
> .Cells(1, 1) = "INDEX"
> .Cells(1, 1).Name = "Index"
> End With
>
> For Each wSheet In Worksheets
> If wSheet.Name <> Me.Name Then
> l = l + 1
> With wSheet
> .Range("A1").Name = "Start" & wSheet.Index
> .Hyperlinks.Add Anchor:=.Range("A1"), Address:="", _
> SubAddress:="Index", TextToDisplay:="Back to Index"
> End With
>
> Me.Hyperlinks.Add Anchor:=Me.Cells(l, 1), Address:="", _
> SubAddress:="Start" & wSheet.Index, TextToDisplay:=wSheet.Name
> End If
> Next wSheet
> End Sub
>
> The only problem i am having is i want the sheet to obtain various cell
> values from each sheet and then place them in the relevant columns.
>
> Does anyone have any ideas?
>
> Thank you in advance.