I have many worksheets in my spreadsheet. In my first worksheet, I want to write a list of all the worksheets starting from column A1, and then in column A2, I want to be able to run a formula to link the cell in A1 to a specific set of cells in the other worksheet.
Thank you very much in advance.
Re: Return Data From Referencing A Particular Cell And Worksheet
Hi
and welcome
Right click on the first sheet in your workbook and copy this
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 SubThis will list all sheets in the workbook and will create a hyperlink to each sheet. It will also create a hyperlink in A1 on each sheet sHowing "Back to Index". You may wish to suppress this.
In order to achieve the second part of your requirement amend the Range("A1") to your needs
This code was suggested to me a couple of years ago by a member of this forum to whom all the credit should go
Hope this helps
Robert
Don’t have an account yet? Register yourself now and be a part of our community!