Creating an Index Sheet that obtains a cell Value using VBA

[David Coyle]

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.

 

[Bernie Deitrick]

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