Pulling data to index sheet and sorting.

[coyotenorth]

I am working with a recipe book and having problem with code for returning value to index page. My index page have this code:
"Private Sub Worksheet_Activate()
Dim wSheet As Worksheet
Dim M As Long
M = 3
With Me
.Columns(1).ClearContents
.Cells(1, 1) = ""
.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("x1"), Address:="", SubAddress:="Index", TextToDisplay:="Tilbake til oppskrifter"
End With
Me.Hyperlinks.Add Anchor:=Me.Cells(M, 1), Address:="", SubAddress:="Start" & wSheet.Index, TextToDisplay:=wSheet.Name
End If
Next wSheet

Columns("A:A").Select
With Selection.Font
.Size = 16
.Strikethrough = False
.Superscript = False
.Subscript = False
.OutlineFont = False
.Shadow = False
.Underline = False
.TintAndShade = 0
End With

With Selection.Font
.ThemeColor = xlThemeColorLight1
.TintAndShade = 0
End With

Range("A4").Select
ActiveWorkbook.Worksheets("Recipe").Sort.SortFields.Clear
ActiveWorkbook.Worksheets("Recipe").Sort.SortFields.Add Key:=Range("A4") _
, SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
With ActiveWorkbook.Worksheets("Recipe").Sort
.SetRange Range("A4:A120")
.Header = xlGuess
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With
Selection.AutoFilter

End Sub"
This list all the worksheets, set hyperlink to the sheet and sort them.
What i want to do is to is to get a value from cell B2 in that sheet returned to collum B in index sheet locked to the sheet name.
This could be in VBA code to the index sheet, a macro or formula.
ANY HELP WILL DO!
(Have example file but dont know how to upload it)

 

[DanteAmor]

What i want to do is to is to get a value from cell B2 in that sheet returned to collum B

In the last lines of your code you have the instructions to order the data in the "Recipe" sheet, so I suppose that all the data goes in that sheet.
Try this:

Private Sub Worksheet_Activate()
  Dim wSheet As Worksheet
  Dim m As Long
  
  m = 3
  Me.Range("A:B").ClearContents
  Me.Cells(1, 1) = ""
  Me.Cells(1, 1).Name = "Index"
  
  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("x1"), Address:="", SubAddress:="Index", TextToDisplay:="Tilbake til oppskrifter"
        Me.Hyperlinks.Add Anchor:=Me.Cells(m, 1), Address:="", SubAddress:="Start" & wSheet.Index, TextToDisplay:=wSheet.Name
        Me.Cells(m, 2).Value = .Range("B2").Value
      End With
    End If
  Next wSheet
  
  With Me.Range("A:B").Font
    .Size = 16
    .Strikethrough = False
    .Superscript = False
    .Subscript = False
    .OutlineFont = False
    .Shadow = False
    .Underline = False
    .TintAndShade = 0
    .ThemeColor = xlThemeColorLight1
    .TintAndShade = 0
    End With
  
  Range("A4").Select
  Range("A4:B" & Range("A" & Rows.Count).End(3).Row).Sort key1:=Range("A4"), order1:=xlAscending, Header:=xlNo
End Sub

Note Code Tag:
In future please use code tags when posting code.
How to Post Your VBA Code it makes your code easier to read and copy and it also maintains VBA formatting.

Note to share file:
You could upload a copy of your file to a free site such www.dropbox.com or google drive. Once you do that, mark it for 'Sharing' and you will be given a link to the file that you can post here. If the workbook contains confidential information, you could replace it with generic data.
---

 

[coyotenorth]

Tks. Here i link to google drive.

Google Drive: Sign-in

Access Google Drive with a Google account (for personal use) or Google Workspace account (for business use).

drive.google.com

 

[DanteAmor]

But, did you try the code from post #2?
You should try it and put your comments here...

 

[coyotenorth]

This seems to work very well. I wil be working with this a while. Thank you