Creating an Index Sheet that obtains a cell Value using VBA
 

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.

Creating an Index Sheet that obtains a cell Value using VBA
 

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" wrote in message
...
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.